How to Use If/Else Statement to Update or Create New Xml Node Entry in SQL

  • Home
  • Languages
  • SQL
  • How to Use If/Else Statement to Update or Create New Xml Node Entry in SQL

How to use IF/ELSE statement to update or create new xml node entry in Sql

Try to delete the anchor element first and then insert the new one. It does not matter if it is there or not for the delete statement. I also provided a better way to build your new anchor element. It takes care of creating entities for characters like &.

-- Delete the anchor node from the XML
set @xml.modify('delete /root/StartOne/Value6/a');

-- Build the XML for the new anchor node
set @a = (
         select @locTitle as 'a/@title',
                @locUrl as 'a/@href',
                '_blank' as 'a/@target',
                @locTitle as 'a'
         for xml path(''), type
         );

-- Insert the new anchor node
set @xml.modify('insert sql:variable("@a") into (/root/StartOne/Value6)[1]');

Update Empty XML Tag in SQL Server

As far as I know from my own experience, you cannot do this in one step, since the <AgencyID/> element really has no text() - so therefore, you cannot replace it.

You might have to use something like:

DECLARE @newValue VARCHAR(100)
SELECT @newValue = '01'

-- first update - add a new <AgencyID>...</AgencyID> node    
UPDATE dbo.UploadReport
SET XmlTest.modify('insert <AgencyID>{sql:variable("@newValue")}</AgencyID> as last into (/CodeFiveReport/Owner)[1]') 
WHERE id = 'myId'

-- second update - remove the empty <AgencyID /> node    
UPDATE dbo.UploadReport
SET XmlTest.modify('delete (/CodeFiveReport/Owner/AgencyID)[1]') 
WHERE id = 'myId'

One thing: you need to use the XmlTest.modify operation - not XmlTest.insert as you have in your post. There is no .insert() function on SQL Server XML columns.

SQL, Find node value in xml variable, if it exists insert additional nodes into xml variable

Check this:

declare @fields xml = '<F><ID>979</ID><ID>1000</ID><ID>989</ID></F>'
    , @add xml = '<ID>992</ID><ID>993</ID>'
;
if @fields.exist('/F[1]/ID[text()="1000"]') = 1
    set @fields.modify('insert sql:variable("@add") as last into /F[1]');

select @fields

How to modify an xml variable with a conditional/where clause

Here is the answer for the "..REAL LIFE case...". I modified the input XML by adding some additional elements. The XQuery was adjusted accordingly.

SQL

-- DDL and sample data population, start
DECLARE @xml xml = N'<Container>
  <city>Miami</city>
  <state>FL</state>
  <Collection>
    <foo>One</foo>
    <bar>true</bar>
    <baz>false</baz>
  </Collection>
  <Collection>
    <foo>Two</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
  <Collection>
    <foo>Three</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
</Container>';
-- DDL and sample data population, end

-- before
SELECT @xml AS [before];

-- update single element
SET @xml.modify('replace value of (/Container/Collection[upper-case((foo/text())[1]) = "TWO"]/bar/text())[1] with "false"')

-- after
SELECT @xml AS [After];

-- Method #1, via FLWOR expression
-- update all <bar> elements with the false' value
DECLARE @bar VARCHAR(10) = 'false';

SET @xml = @xml.query('<Container>
{
   for $x in /Container/*[not(local-name(.)=("Collection"))]
   return $x
}
{
    for $y in /Container/Collection
    return <Collection>
    {
        for $z in $y/*
        return 
        if (not(local-name($z) = ("bar"))) then $z
        else 
        (
            element bar {sql:variable("@bar")}
        )
    }
    </Collection>
}
</Container>');

Inserting node in XML using SQL

No need for any complicated hassel. Just insert the node you want as you want it:

UPDATE #temp SET xml_data.modify('insert <type>Q</type> into (/Main)[1]');

Using as first, as last or before / after allows you to specify the node's position. The following will place the new node directly after <name>:

UPDATE #temp SET xml_data.modify('insert <type>Q</type> after (/Main/name)[1]');

UPDATE Your question about an update-statement

Your statement has several flaws:

UPDATE #temp
     SET xml_data = 
        case
            when @type = 'G' 
            then xml_data.modify('insert <type>G</type> into (/Main)[1]');
            when @type = 'Q' 
            then xml_data.modify('insert <type>Q</type> into (/Main)[1]'); 
         end

You cannot use the syntax SET xmlColumn = xmlColumn.modify(). You have to use SET xmlColumn.modify(), Furthermore the semicolons are breaking this anyway.

To be honest, I think this is to complicated, try this: 

DECLARE @type VARCHAR(1)='Q'
UPDATE #temp SET xml_data.modify('insert <type>{sql:variable("@type")}</type> into (/Main)[1]');

This will create a new node <type>content</type>, with a content taken out ot the variable @type.

T-SQL XML : how to update value in one node if there are more nodes with identical names?

Admittedly, this does not sort the XML string in order of number, but you should be able to work it in to how you need it:

update b
-- .modify is a special function that modifies XML in place
set SomeXMLData.modify('
    replace value of
    (/Parameters/Parameter[Key[text()="ABC"]]/Value/text())[1]
    with
    concat (
        (/Parameters/Parameter[Key[text()="ABC"]]/Value/text())[1],
        if ((/Parameters/Parameter[Key[text()="ABC"]]/Value[contains(text()[1], "3")])[1] ) then "" else ", 3" ,
        if ((/Parameters/Parameter[Key[text()="ABC"]]/Value[contains(text()[1], "5")])[1] ) then "" else ", 5" 
    )')
from @beforeXML b;

The way this works is as follows:

  1. We search for XML nodes, starting at root /, descending Parameters, then Parameter but this node must have a sub-node Key which has a text()="ABC", then descend /Value/text()) and take the first [1] node.
  2. Replace this value with the concatenation of the existing value, and:
  3. if Value node matches [contains(text()[1], "3")])[1] ) then nothing, else we add ", 3"
  4. if Value node matches [contains(text()[1], "5")])[1] ) then nothing, else we add ", 5"

Result:

|SomeDataXML|
----
|<Parameters><Parameter><Key>ABC</Key><Value>1, 2, 4, 3, 5</Value></Parameter><Parameter><Key>XYZ</Key><Value>A</Value></Parameter></Parameters>|
|<Parameters><Parameter><Key>KLM</Key><Value>true</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 4, 5, 3</Value></Parameter></Parameters>|
|<Parameters><Parameter><Key>KLM</Key><Value>false</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 3, 6, 5</Value></Parameter><Parameter><Key>XYZ</Key><Value>A, C</Value></Parameter></Parameters>|
|<Parameters><Parameter><Key>XYZ</Key><Value>A</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 3, 5</Value></Parameter></Parameters>|
|<Parameters><Parameter><Key>XYZ</Key><Value>B</Value></Parameter><Parameter><Key>KLM</Key><Value>true</Value></Parameter></Parameters>|

How do I set a variable in an UPDATE statement, then use that variable in a xml.modify() statement?

@GSerg offered an alternate solution which works perfectly -- elimintate the variable and use sql:column("LT.lookup_value") rather than sql:variable().

UPDATE #dest_table
SET xml_data.modify('insert text{sql:column("LT.lookup_value")} into (./root/value)[1]')
FROM #dest_table DT
INNER JOIN #lookup_table LT
ON DT.lookup_key = LT.lookup_key;

Assuming others are as well, I am still very interested to understand why the solution provided in the question does not work.

Update an XML node that is stored as string in Database

You can try the following methods.

It shows how to modify the <salary> element value when:

  • xmldata column is XML data type.
  • xmldata column is NVARCHAR(MAX) data type.

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl ( xmldata) VALUES
(N'<?xml version="1.0" encoding="utf-16"?>
<EmployeeDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Name>MyName</Name>
    <Age>20</Age>
    <Salary>15000</Salary>
</EmployeeDetail>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @salary VARCHAR(10) = '10000';

UPDATE @tbl
SET xmldata.modify('replace value of 
    (/EmployeeDetail/Salary/text())[1] 
    with (sql:variable("@salary"))
');

-- after
SELECT * FROM @tbl;

SQL #2

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata NVARCHAR(MAX));
INSERT INTO @tbl ( xmldata) VALUES
(N'<?xml version="1.0" encoding="utf-16"?>
<EmployeeDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Name>MyName</Name>
    <Age>20</Age>
    <Salary>15000</Salary>
</EmployeeDetail>');

DECLARE @realXML TABLE (ID INT, realXML XML);
-- DDL and sample data population, end

-- populate temporary table
INSERT INTO @realXML
SELECT ID, cast(xmldata AS XML)
FROM @tbl;

DECLARE @salary VARCHAR(10) = '10000';

/*
Unfortunately, we can not assign from a xml.modify() statement. 
Modify works on a variable and a column only. 
Also, we cannot use modify on a CAST().
*/
-- update temporary table
UPDATE @realXML
SET realXML.modify('replace value of 
            (/EmployeeDetail/Salary/text())[1] 
            with (sql:variable("@salary"))
        ');

-- update real table 
UPDATE tbl
SET xmldata = CAST(t.realXML AS NVARCHAR(MAX))
FROM @realXML AS t INNER JOIN @tbl AS tbl
ON tbl.ID = t.ID;

Update and Insert into SQL Server database using XML via C# WPF App

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data;

namespace ConsoleApplication193
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        const string Path = "Enter your path here";
        static void Main(string[] args)
        {
            //string FILENAME = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Connection.xml");
            XDocument xdoc = XDocument.Load(FILENAME);
            using (SqlConnection con = new SqlConnection("Add your connection string here"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = " INSERT INTO [dbo].[tblEMP] (EMPID, EMPName, EMPRole, EMPAddress, EMPMail, EMPNumber) VALUES (@EMPID, @EMPName, @EMPRole, @EMPAddress, @EMPMail, @EMPNumber)";
                    cmd.Parameters.Add("@EMPID", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@EMPName", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@EMPRole", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@EMPAddress", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@EMPMail", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@EMPNumber", SqlDbType.NVarChar);
                   
                    foreach (XElement ry in xdoc.Descendants("ry"))
                    {
                        string empId = (string)ry.Element("EMPID");
                        string empName = (string)ry.Element("EMPName");
                        string empRole = (string)ry.Element("EMPRole");
                        string empAddress = (string)ry.Element("EMPAddress");
                        string empEmail = (string)ry.Element("Email");
                        string empNumber = (string)ry.Element("EMPNumber");

                        cmd.Parameters["@EMPID"].Value = empId;
                        cmd.Parameters["@EMPName"].Value = empName;
                        cmd.Parameters["@EMPRole"].Value = empRole;
                        cmd.Parameters["@EMPAddress"].Value = empAddress;
                        cmd.Parameters["@EMPMail"].Value = empEmail;
                        cmd.Parameters["@EMPNumber"].Value = empNumber;
 
                        cmd.ExecuteNonQuery();

                    }
                }
            }
 
        }
    }

}

sql query to update xml node stored in a column

You could use an insert/delete approach.

Your code did not throw any error for me on SQL2008R2.
But it did NOT give the desired result either.

The statement resulted in replacing the text value of the node, not in replacing the node itself, as you can see below.

<page name="page0" identifier="ff-102-101-101">
  <backBut><testnode>test value</testnode></backBut>
  <printBut>Print</printBut>
  <quiz1>Click on the circle that best describes your</quiz1>
  <quiz2>Continue</quiz2>
  <quiz3>Finish</quiz3>
  <quiz4>You are now on questions </quiz4>
  <quiz5>out of</quiz5>
  <quiz6>Please answer each question before continuing.</quiz6>
</page>

You could achieve it via insert/delete:

First insert the new node:

UPDATE tblCCBT_Step_Page_Text_Xml
SET Xml_XmlData.modify('insert <testnode>test value</testnode> into /page[1]')
WHERE xml_id = 101

Then simply delete the old node:

UPDATE tblCCBT_Step_Page_Text_Xml
SET Xml_XmlData.modify('delete (/page/backBut)[1]')
WHERE xml_id =101

Related Topics

Get Avg Ignoring Null or Zero Values

Solution to "Cannot Perform a Dml Operation Inside a Query"

Inner Joining Three Tables

How to Return a Incremental Group Number Per Group in SQL

Previous Monday & Previous Sunday's Date Based on Today's Date

Mysql: Typecasting Null to 0

Doesn't Linq to SQL Miss the Point? Aren't Orm-Mappers (Subsonic, etc.) Sub-Optimal Solutions

The Transaction Log for the Database Is Full

Oracle SQL: Update If Exists Else Insert

Get Values from First and Last Row Per Group

Update Query Using Subquery in SQL Server

Delete All But Top N from Database Table in SQL

How to Use Sum() Over()

Redundant Data in Update Statements

SQL on Delete Cascade, Which Way Does the Deletion Occur

Retrieve Column Names and Types of a Stored Procedure

SQL Server 2005 and Temporary Table Scope

Execute a Stored Procedure in Another Stored Procedure in SQL Server


Leave a reply



Submit