Sql Server SQL XML DML: How to INSERT a new node or update value in non-existing node.

Quick one today:

Continuing the series of posts on XML DML, today, we’ll look at updating/replacing value in an non-existing node (or INSERTing a new node into an existing element).

In the past two posts, we saw how to update (or replace ) values in XML nodes.

1. The first one is about updating value in a simple untyped XML updates.
2. Second one has two sections:
   1. Update (or replace) element attribute value in a typed XML. See the code under section ‘Side story for context : Update Attribute value‘
   2. Update node value of typed XML section
3. Third one is about updating value in an empty node.

Today, we’ll see how to update a non-existing node or INSERTing a new node into an existing element.

Let’s use the same code as before to set the stage:

--
-- Create new sample table
--
CREATE TABLE dbo.SampleXML (
ID INT NOT NULL IDENTITY(1,1)
, XML_Col XML
)
GO

--
-- Create XML value
--
DECLARE   @X XML = '
        <Root>
            <ParentElement Att1="Color" Att2="10">
                <Child1>Marco</Child1>
                <Child2>Polo</Child2>
            </ParentElement>
            <ParentElement Att1="Brand" Att2="16">
                <Child1>Hello</Child1>
                <Child2></Child2>
            </ParentElement>
            <ParentElement Att1="Style" Att2="8">
                <Child1>Hello</Child1>
            </ParentElement>
        </Root>'

--
-- INSERT XML value into table column
--
INSERT INTO dbo.SampleXML (XML_Col) VALUES (@X)
GO

--
-- See the result
--
SELECT *
FROM dbo.SampleXML
GO

Sample XML file looks like this:

Sample XML to update non-existing node

Lets try to update the section in red in the image above.

Update non-existing Node or INSERT a new node into existing element

As it is obvious by now, that updating value of an non-existing node in an element is same as INSERTing a new node. The reason, non-existing aspect is highlighted is because, this XML example has multiple <ParentElement> elements, that have similar structure.

• First ParentElement has all two nodes with values
• Second element only has data in first node; Second child node is empty.
• Third element also only has data in first node; Second child node does not exist.

In this example, lets work on the third scenario:

--
-- INSERT new node
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
			insert <Child2>Contemporary<Child2>
			into (/Root/ParentElement[@Att1="Style"])[1]
		')
WHERE XML_Col.exist('/Root/ParentElement[@Att1="Style"]/Child2/text()') = 0

INSERT New Node

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server SQL XML DML: Update or replace value in empty node

Quick one today:

Continuing the series of posts on XML DML, today, we’ll look at updating/replacing value in an empty node.

In the past two posts, we saw how to update (or replace ) values in XML nodes.

1. The first one is about updating value in a simple untyped XML updates.
2. Second one has two sections:
   1. Update (or replace) element attribute value in a typed XML.
   2. Update node value of typed XML section

Today, we’ll see how to update when the node is empty.

Let’s see some code to create sample table with XML column with an XML record.

--
-- Create new sample table
--
CREATE TABLE dbo.SampleXML (
ID INT NOT NULL IDENTITY(1,1)
, XML_Col XML
)
GO

--
-- Create XML value
--
DECLARE   @X XML = '
        <Root>
            <ParentElement Att1="Color" Att2="10">
                <Child1>Marco</Child1>
                <Child2>Polo</Child2>
            </ParentElement>
            <ParentElement Att1="Brand" Att2="16">
                <Child1>Hello</Child1>
                <Child2></Child2>
            </ParentElement>
            <ParentElement Att1="Style" Att2="8">
                <Child1>Hello</Child1>
            </ParentElement>
        </Root>'
--
-- INSERT XML value into table column
--
INSERT INTO dbo.SampleXML (XML_Col) VALUES (@X)
GO

--
-- See the result
--
SELECT *
FROM dbo.SampleXML
GO

Sample XML file looks like this:

Sample XML to update empty node

 

Lets try to update the section in green in the image above.

Update Empty Node

When a node is empty, XML DML works differently. We cannot use one of the methods mentioned in previous posts (here & here).

For XML DML the node (‘/Root/ParentElement[@Att1=”Brand”]/Child2/text())[1]‘) does not exist. So, when we try to look it up, nothing comes back to be updated. The frustrating part is the message that says “(1 row(s) affected)“, when nothing got updated. So after some head banging on desk exercise, this came up.

--
-- Update an empty node
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
		insert text{"Dockers"}
		into (/Root/ParentElement[@Att1="Brand"]/Child2)[1]
		')
WHERE XML_Col.exist('/Root/ParentElement[@Att1="Brand"]/Child2/text()') = 0

 

 

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Error Message : Replacing the value of a node with an empty sequence is allowed only if ‘()’ is used as the new value expression. The new value expression evaluated to an empty sequence but it is not ‘()’

Quick one today:

As a follow up to one of the previous posts on XML DML, this post goes into the next steps of the error message, if you have a typed XML column.

When you perform UPDATE (replace value of) on a typed XML column, sometimes you might run into this error:

Msg 6325, Level 16, State 1, Line 1
 XQuery: Replacing the value of a node with an empty sequence is allowed only 
if '()' is used as the new value expression. The new value expression evaluated to an 
empty sequence but it is not '()'.

T-Sql that throws this error:

--
-- Replace 'World' with 'Universe' in <ParentElement>..<Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
		replace value of (/Root/ParentElement[@Att1='Brand']/Child2/text())[1]
		with Universe
')
WHERE ID = 1
GO

Repro Steps

First look at the code to replicate the issue:

--
-- Create new sample table
--
CREATE TABLE dbo.SampleXML (
	  ID		INT NOT NULL IDENTITY(1,1)
	, XML_Col	XML
)
GO

--
--	Create XML value
--
DECLARE   @X XML = '
        <Root>
            <ParentElement Att1='Color' Att2='10'>
                <Child1>Marco</Child1>
                <Child2>Polo</Child2>
            </ParentElement>
            <ParentElement Att1='Brand' Att2='16'>
                <Child1>Hello</Child1>
                <Child2>World</Child2>
            </ParentElement>
        </Root>'
--
-- INSERT XML value into table column
--
INSERT INTO dbo.SampleXML (XML_Col) VALUES (@X)
GO

--
-- See the result
--
SELECT *
FROM dbo.SampleXML
GO

 

T-Sql that throws this error:

--
-- Replace 'World' with 'Universe' in <ParentElement> / <Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
	replace value of (/Root/ParentElement[@Att1='Brand']/Child2/text())[1]
	with Universe
')
WHERE ID = 1
GO

 

Side story for context : Update Attribute value

In this UPDATE statement, we are attempting to update/replace value present in a node’s text. So, we need to approach it with caution. If it were updating/replacing value of an attribute, it would be straight forward. See example below:

--
--  Updating attribute '@Att2' value
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
	replace value of (/Root/ParentElement[@Att1='Brand']/@Att2)[1]
	with '32'
')
WHERE ID = 1
GO

Solution

Unlike the above attribute example, here we are attempting to update the text() portion of a node. So, XML standard expects:

• Conditions (if…else) to make sure correct XML element is identified & updated;
• And it has a default value to fall back to in case there are any errors.

So, with some minor changes, the new code looks like this:

Version 1:

--
-- Replace 'World' with 'Universe' in <ParentElement> / <Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
		replace value of (/Root/ParentElement[@Att1='Brand']/Child2/text())[1]
		with (
				if (/Root/ParentElement[@Att1='Brand']/Child2)
					then 'Universe'
				else
					'World'
			 )
	')
WHERE ID = 1
GO

Here are are

• Identifying the exact section to ‘replace’ (/Root/ParentElement[@Att1=”Brand”]/Child2).
  • Then, we replace existing value with new value (Universe)
• If the IF condition fails, for any reason, we have a fail back
  • update existing value with something else (in this case, same value ‘World’)

In some rare occasions, it might be needed to write the else clause slightly differently:

Version 2:

--
-- Replace 'World' with 'Universe' in <ParentElement2> / <Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
			replace value of (/Root/ParentElement[@Att1='Brand']/Child2/text())[1]
			with (
				if (/Root/ParentElement[@Att1='Brand']/Child2)
					then 'Universe'
				else
					data(/Root/ParentElement[@Att1='Brand']/Child2)
				)
 		')
WHERE ID = 1
GO

In Version 2, we are essentially saying the same thing as (version 1) above, but with more code. If (/Root/ParentElement[@Att1=”Brand”]/Child2) exists, then use “Universe”, else use the existing value.

Happy XML DML!!

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server XML : Error Message : XQuery [dbo.SampleXML.XML_Col.modify()]: The target of ‘replace value of’ must be a non-metadata attribute or an element with simple typed content, found ‘element(Child2,xdt:untyped) ?’

Quick one today:

Earlier, while playing with XML DML on our lab environment, this interesting error came up. XML DML is both complicated and interesting. It has some confusing and clever nuances that make manipulating XML data both frustrating and fun.

Error Message:

Msg 2356, Level 16, State 1, Line 5
XQuery [dbo.SampleXML.XML_Col.modify()]: The target of 'replace value of' must be a non-metadata 
attribute or an element with simple typed content, found 'element(Child2,xdt:untyped) ?'

Solution is just an interesting as the problem.

First look at the code to replicate the issue:

--
-- Create new sample table
--
CREATE TABLE dbo.SampleXML (
	  ID		INT NOT NULL IDENTITY(1,1)
	, XML_Col	XML
)
GO

--
--	Create XML value
--
DECLARE   @X XML = '
		<Root>
			<ParentElement Att1="Color" Att2="10">
				<Child1>Marco</Child1>
				<Child2>Polo</Child2>
			</ParentElement>
			<ParentElement Att1="Brand" Att2="16">
				<Child1>Hello</Child1>
				<Child2>World</Child2>
			</ParentElement>
		</Root>'
--
-- INSERT XML value into table column
--
INSERT INTO dbo.SampleXML (XML_Col) VALUES (@X)
GO

--
-- See the result
--
SELECT *
FROM dbo.SampleXML
GO

 

Now, lets try to replace ‘World‘ in Child2 element of second ParentElement.

T-Sql that throws error:

--
-- Replace 'World' with 'Universe' in <ParentElement2> / <Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
		replace value of (/Root/ParentElement[@Att1="Brand"]/Child2)[1]
		with Universe
		')
WHERE ID = 1
GO

Boom!! You get this error.

Msg 2356, Level 16, State 1, Line 5
XQuery [dbo.SampleXML.XML_Col.modify()]: The target of 'replace value of' must be a non-metadata 
attribute or an element with simple typed content, found 'element(Child2,xdt:untyped) ?'

Solution

The error message is conveying that the query path ‘(/Root/ParentElement[@Att1=”Brand”]/Child2)‘ in ‘replace value of’ statement is not correct.

The query path must result in values in the XML file, not elements or nodes (which are meta-data).

Adding /text() to its query path shows that we want to update the values in XML file and not the XML nodes or elements (meta-data, which is not allowed).

T-Sql that works (for untyped):

--
-- Replace 'World' with 'Universe' in <ParentElement2> / <Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
		replace value of (/Root/ParentElement[@Att1="Brand"]/Child2/text())[1]
		with Universe
	')
WHERE ID = 1
GO
GO

Note: This works, if you have simple untyped XMLs. But, if you have typed XML you need to add few more expressions to make the XML work. That will be a follow up post.

 

Happy XML DML!!

 

Hope this helps,
_Sqltimes

Read Full Post »