Feeds:
Posts
Comments

Posts Tagged ‘XML DML’

Interesting one today:

In the past, we’ve seen multiple posts on XML DML covering several XML manipulation capabilities. Today, we’ll cover another aspect of XQuery : Ability to add multiple conditions within the same XQuery path.

--
-- 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 = '

				Marco
				Polo

				Hello 16
				World 16

				Hello 17
				World 17

				Hello

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

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

The sample XMl looks like this:

XMLDML_MultipleConditions

And in it we’ll be querying the following things:

  1. Using Single Condition
    1. Query the number of ParentElement under Root
    2. Query the number of ParentElement with @Att1 = “Color”
      1. Answer: 1
    3. Query the number of ParentElement with @Att1 = “Brand”
      1. Answer: 2
    4. Retrieve element text of ParentElement with @Att1 = “Brand” for Child1
      1. Answer: Hello 16
  2. Using Two conditions
    1. Retrieve element text of ParentElement with @Att1 = “Brand” and @Att2=”17″ for Child1
      1. Answer: Hello 17

XMLDML_MultipleConditions_Detailed.png

--
-- XML Query to retrieve with multiple conditions in XQuery<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
--
SELECT	  XML_Col.value('count(/Root/ParentElement)', 'INT')													AS [NumberOf_ParentElements]
		, XML_Col.value('count(/Root/ParentElement[@Att1="Color"])', 'INT')										AS [NumberOf_ParentElements_Color_Attribute]
		, XML_Col.value('count(/Root/ParentElement[@Att1="Brand"])', 'INT')										AS [NumberOf_ParentElements_Brand_Attribute]

		, XML_Col.value('(/Root/ParentElement[@Att1="Brand"]/Child1/text())[1]', 'VARCHAR(30)')					AS [Child1_Text_in_Brand]
		, XML_Col.value('(/Root/ParentElement[@Att1="Brand" and @Att2="17"]/Child1/text())[1]', 'VARCHAR(30)')	AS [Child1_Text_in_Brand_and_17]

FROM dbo.SampleXML
GO

Output looks like this:

XMLDML_MultipleConditions_Output

 

Hope this helps,
_Sqltimes

 

Advertisements

Read Full Post »

Quick one today

Once in a while, we need to process XML files (a.k.a. deadlock XML files) to retrieve some pertinent information to uncover the deadlock patterns.

In the past, we’ve seen some XML DML queries to parse XML files.

Here we’ll focus on filtering capability based on attribute values in elements:

Take an deadlock XML for example:

Deadlock_Filter_Attribute

The goal is to search for the action element with attribute value as “collect_system_time“.

--
-- Parse & Filter XMl file data
--
SELECT
	  ROW_NUMBER() OVER(ORDER BY DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2'))											AS [RowN]
	, DeadLockXML.value('(/event/action[@name="collect_system_time"]/value)[1]', 'DATETIME') AS SystemTime
FROM  dbo.Deadlocks_07262018_Step2
GO

The [] allow us to provide the value to filter the XMl elements:

(/event/action[@name="collect_system_time"]/value)[1]'

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Quick one today:

XML DML is interesting feature of Sql Server. It is vast and has lots of features in manipulating XML files and its elements & attributes. Today, we’ll look at exists function to check if a particular element exists or an element with a specific attribute exists.

Let’s use this below XML as a sample:

DECLARE   @X XML = '

                Marco
                Polo

                Hello

                Hello

        '

 

XMLDML_Exists_Sample.PNG

Let’s look at a way to check if

  • If a “ParenetElement” element with “Style” attribute exists
  • If a “Child” element with “Polo” as element text value exists

Check at Attribute Level

--
-- Check if ParentElement with Attribute value of "Style" exists
--
DECLARE   @X XML = '

                Marco
                Polo

                Hello

                Hello

        '
SELECT @X.exist('(/Root/ParentElement[@Att1="Style"])') AS [Exists_Or_Not]
GO

XMLDML_Exists

Check Element Text Level

--
-- If a "Child" element with "Polo" as element text value exists
--
DECLARE   @X XML = '

                Marco
                Polo

                Hello

                Hello

        '
SELECT @X.exist('(/Root/ParentElement/Child2[(text()[1] = "Polo" )])') AS [Polo_Exists?]
GO

XMLDML_Exists2.PNG

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

In the recent past, we’ve seen several XML posts. Today, we’ll cover a way to check if an element text is empty. Let’s take a sample XML file

XMLDML_Exists_Sample

Lets check if the Child2 element in Brand ParentElement is empty or not

XMLDML_EmptyElement

--
-- Check if an element is empty.<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
--
DECLARE   @X XML = '
        
            
                Marco
                Polo
            
            
                Hello
                
            
            
                Hello
            
        '
--SELECT @X.value('(/Root/ParentElement[@Att1="Brand"]/Child1)[1]', 'VARCHAR(20)')
IF	(
		SELECT @X.value('(/Root/ParentElement[@Att1="Brand"]/Child2)[1]', 'VARCHAR(20)')
	) = ''
	PRINT 'Empty'
ELSE
	PRINT 'Not Empty'

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Interesting one today:

XML DML is one powerful tool in Sql Server arsenal. In that value() function allows us to parse the XMl file and retrieve the values of attributes, elements, text, etc from the XML.

Using the sample XML below, we’ll explore various versions of using value() function.

Examples:

This is the sample XML file

XML_DML_Structure.PNG

DECLARE @X XML = '

....
'

SELECT @X.value('(/StoreConfigurations/ProcessingOptions/PasswordExpirationDays)<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>[1]', 'VARCHAR(2)') AS [Returns30]
	, @X.value('(/StoreConfigurations/ProcessingOptions/PasswordExpirationDays)[2]', 'VARCHAR(2)') AS [ReturnsNULL]

	, @X.value('(/StoreConfigurations/ReceiptTexts/ReceiptText/ReceiptHeaderLine1)[1]', 'VARCHAR(100)') AS [Returns YourStore]
	, @X.value('(/StoreConfigurations/ReceiptTexts/ReceiptText[@StoreNumber="6"]/ReceiptHeaderLine2)[1]', 'VARCHAR(100)') AS [Returns Your Street]

	, @X.value('(/StoreConfigurations/Hosts/Host/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 01]
	, @X.value('(/StoreConfigurations/Hosts/Host[2]/RetryCounter)[2]', 'VARCHAR(2)') AS [Returns 04]
	, @X.value('(/StoreConfigurations/Hosts/Host[3]/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 03]

	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="X"]/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 01]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Y"]/RetryCounter)[2]', 'VARCHAR(2)') AS [Returns 04]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Z"]/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 03]

	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="X"]/HostERCUse)[1]', 'VARCHAR(2)') AS [Returns X]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Y"]/HostERCUse)[1]', 'VARCHAR(2)') AS [Returns Y]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Z"]/HostERCUse)[1]', 'VARCHAR(2)') AS [Returns Z]

--/ReceiptHeaderLine1
IF (@X.value('(/StoreConfigurations/Hosts/Host[@Prefix="X"]/HostERCUse)[1]', 'VARCHAR(2)') = 'X')
	PRINT 'Yes, its X'
IF (@X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Y"]/HostERCUse)[1]', 'VARCHAR(2)') = 'Y')
	PRINT 'Yes, its Y'
IF (@X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Z"]/HostERCUse)[1]', 'VARCHAR(2)') = 'Z')
	PRINT 'Yes, its Z'
GO

Output:

The result looks like this:

XML_DML_Value

Also we could PRINT from the value function too:

XML_DML_Value_Print.PNG

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Recently, we capture deadlock server side traces and needed a quick way to retrieve all SPIDs and procedures involved in deadlocks.

The deadlock trace graph is an XML file. If there was one or two deadlocks, we could amanyze it manually. But when you have several deadlocks (100’s or more) and need to analyze all the instances of deadlock entries in the trace, we need a query to parse the XML tree.

Enter XML DML!!

Sample Deadlock XML file looks like this:

We need to retrieve the procedure name involved in the deadlock. Use value() function to parse through the XML path and retrieve the attribute or element value.

Attribute value using @AttributeName

--
--	Return Attribute value in an XML element
--
SELECT	  CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[3]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[4]', 'VARCHAR(100)')
		, CONVERT(XML, TextData)
FROM [After]
WHERE EventClass = 148

Pay attention the path mentioned — that shows what value to retrieve:

/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[1]

  • ‘/deadlock-list/deadlock/process-list/process/executionStack/frame/’ is the location of the attribute
  • ‘@procname’ is the attribute to retrieve
  • [1] says to retrieve the vale from the first instance of such element — as XML could have multiple repeating similar elements. So we could also say [2], [3]… for respective instances.
  • We mention datatype VARCHAR(30) as a second parameter to value function indicating the type of value we are returning.

Attribute text:

Similarly, we could retrieve text value using XML value function:

--
--	Return XML element value
--
SELECT	  CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame)[1]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame)[2]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame)[3]', 'VARCHAR(100)')
		, CONVERT(XML, TextData)
FROM [After]
WHERE EventClass = 148

Similarly, pay attention the path mentioned — that shows text to retrieve:

‘/deadlock-list/deadlock/process-list/process/executionStack/frame)[1]’

  • /deadlock-list/deadlock/process-list/process/executionStack/frame/ is the location of the text
  • [1] says to retrieve the vale from the first instance of such element — as XML could have multiple repeating similar elements. So we could also say [2], [3]… for respective instances.

For more information, please review MSDN article.

 

Hope this helps,
_Sqltimes

 

Read Full Post »

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

Sample_XML_for_UPDATE

 

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 »

Older Posts »