Feeds:
Posts
Comments

Archive for the ‘XML’ Category

Quick one today:

Quite often, we all run into this scenario where we need to convert column data into row with either comma or some other delimiter.

Below, we’ll see a couple of ways; Each serving a different purpose:

Build data set first:

--
--  Convert row data into column (without using PIVOT)
--

-- Create tables
CREATE TABLE Parent (
	  ID	INT			IDENTITY(1,1)
	, Name	VARCHAR(20)
)

CREATE TABLE Child (
	  ID		INT		IDENTITY(1,1)
	, ParentID	INT
	, Name		VARCHAR(25)
)

-- Create Parent records
INSERT INTO Parent (Name) VALUES ('Mary')
INSERT INTO Parent (Name) VALUES ('Patel')
INSERT INTO Parent (Name) VALUES ('Risvic')

-- Create child records
INSERT INTO Child (ParentID, Name) values (1, 'Jil')
INSERT INTO Child (ParentID, Name) values (1, 'Jim')
INSERT INTO Child (ParentID, Name) values (1, 'Kat')

INSERT INTO Child (ParentID, Name) values (2, 'Raja')
INSERT INTO Child (ParentID, Name) values (2, 'Kamat')
GO

SELECT * FROM Parent
SELECT * FROM Child
GO

Data set looks like this:

RowToColumn_DataSet

Option 1 : Using Variable

This method gives us result in comma delimited fashion, but it only works for one parent at a time; And we cannot combine Parent name as a column next to Children column, as Sql Server does not allow combining data-retrieval with variable manipulation query.

It spits out an error like this:

Msg 141, Level 15, State 1, Line 35
A SELECT statement that assigns a value to a variable must not be 
combined with data-retrieval operations.
--
--	Option 1 : using @Variable
--
DECLARE @Children VARCHAR(8000)

SELECT @Children = COALESCE(@Children, '') + ', ' + Name
FROM dbo.Child
WHERE ParentID = 1

SELECT STUFF(@Children, 1, 2, '')
GO

RowToColumn_Option1

Option 2 : Using XML

XML PATH gives us more control over retrieval manipulation. It allows us to retrieve data in a tabular fashion, while keeping the children names into one column.

Breakdown:

This below query has 3 parts:

  1. Main warp-around query (Parent)
  2. STUFF section
  3. XML PATH section
--
--	Using XML
--
SELECT    P.ID	AS [ParentID]
	, P.Name	AS [ParentName]
	, STUFF(
				(
					SELECT ', ' + C.Name
					FROM dbo.Child AS C
					WHERE C.ParentID = P.ID
					FOR XML PATH('')
				)
				, 1, 2, ''
		  ) AS [Children]
FROM dbo.Parent AS P
GO

--
-- Clean up
--
DROP TABLE Parent
DROP TABLE Child
GO

RowToColumn_Option2

We’ll see the details of each query in reverse order:

XML PATH Query

This is the most important part of this whole query. It allows us to retrieve all children of a given parent in a comma delimited fashion.

Without FOR XML PATH(”) statement, this is just a query that returns children as rows. When you add FOR XML, that data set is now converted into XML format (sort of).

PATH (”) makes sure that the the element trees notation is replaced with whatever is in between ” (empty). So we get a simple comma delimited child list.

Now we have an extra comma to remove. That’s where STUFF comes into play.

STUFF query section replaces comma with empty space.

Main wrap-around query is to bring this all together with ParentID & Parent Name to make it more usable.

 

Hope this helps,
_Sqltimes

 

Advertisements

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:

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:

UPDATE_EmptyNode_NonExistingNode.png

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

INSERT New Node

 

Hope this helps,
_Sqltimes

Read Full Post »

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:

UPDATE_EmptyNode_NonExistingNode.png

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

 

UPDATE_EmptyNode.png

 

 

Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »