Querying XML to display values in table format

Question

Post reply

Querying XML to display values in table format

Sk1ppy

SSC Eights!

Points: 810
More actions
April 4, 2011 at 5:27 pm

#237938

I have a table which has an xml datatype column. I need to query this table and return the data from the xml in a meaningful way.
Let's say the table name is [myXMLDataTable] and the xml field is named [TheXMLData].
Here is a sample value of the [TheXMLData] field:
<RegMultiStringLists>
<RegMultiStringList Name="Type" KeyPath="" KeyFlag="0">
<Value>msdrm.dll</Value>
<Value>CIAgent.DLL</Value>
<Value>CIStore.DLL</Value>
<Value>flash*.ocx</Value>
<Value>flash*.ocx</Value>
</RegMultiStringList>
<RegMultiStringList Name="Path" KeyPath="" KeyFlag="0">
<Value>%windir%\system32\</Value>
<Value>%windir%\SysWOW64\CCM\</Value>
<Value>%windir%\system32\CCM\</Value>
<Value>%windir%\syswow64\Macromed\Flash\</Value>
<Value>%windir%\system32\Macromed\Flash\</Value>
</RegMultiStringList>
<RegMultiStringList Name="Option1" KeyPath="" KeyFlag="0">
<Value>false</Value>
<Value>true</Value>
<Value>true</Value>
<Value>true</Value>
<Value>true</Value>
</RegMultiStringList>
<RegMultiStringList Name="Option2" KeyPath="" KeyFlag="0">
<Value>true</Value>
<Value>true</Value>
<Value>true</Value>
<Value>true</Value>
<Value>true</Value>
</RegMultiStringList>
<RegMultiStringList Name="Option3" KeyPath="" KeyFlag="0">
<Value>false</Value>
<Value>false</Value>
<Value>false</Value>
<Value>false</Value>
<Value>false</Value>
</RegMultiStringList>
</RegMultiStringLists>
I want to know a query that will output the data into columns, kinda like this:
TypePathOption1Option2Option3
msdrm.dll %windir%\system32\FALSETRUEFALSE
CIAgent.dllblahblahblahblah
blah…
Can anyone help me with this? I can't seem to figure out the xml query techniques...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

How about this?

Declare @TheXMLCol XML =

cast( '<RegMultiStringLists> <RegMultiStringList Name="Type" KeyPath="" KeyFlag="0"> <Value>msdrm.dll</Value> <Value>CIAgent.DLL</Value> <Value>CIStore.DLL</Value> <Value>flash*.ocx</Value> <Value>flash*.ocx</Value> </RegMultiStringList> <RegMultiStringList Name="Path" KeyPath="" KeyFlag="0"> <Value>%windir%\system32\</Value> <Value>%windir%\SysWOW64\CCM\</Value> <Value>%windir%\system32\CCM\</Value> <Value>%windir%\syswow64\Macromed\Flash\</Value> <Value>%windir%\system32\Macromed\Flash\</Value> </RegMultiStringList> <RegMultiStringList Name="Option1" KeyPath="" KeyFlag="0"> <Value>false</Value> <Value>true</Value> <Value>true</Value> <Value>true</Value> <Value>true</Value> </RegMultiStringList> <RegMultiStringList Name="Option2" KeyPath="" KeyFlag="0"> <Value>true</Value> <Value>true</Value> <Value>true</Value> <Value>true</Value> <Value>true</Value> </RegMultiStringList> <RegMultiStringList Name="Option3" KeyPath="" KeyFlag="0"> <Value>false</Value> <Value>false</Value> <Value>false</Value> <Value>false</Value> <Value>false</Value> </RegMultiStringList></RegMultiStringLists>' as XML)

; with cte as

(

SELECT Row.value('@Name[1]','VARCHAR(2000)') AS ColName,

Row.value('(Value/text())[1]', 'VARCHAR(2000)') AS [1],

Row.value('(Value/text())[2]', 'VARCHAR(2000)') AS [2],

Row.value('(Value/text())[3]', 'VARCHAR(2000)') as [3] ,

Row.value('(Value/text())[4]', 'VARCHAR(2000)') AS [4] ,

Row.value('(Value/text())[4]', 'VARCHAR(2000)') AS [5]

FROM @TheXMLCol.nodes('//RegMultiStringLists/RegMultiStringList') R(Row)

),

unpivoted_data as

(

select *

from cte real_table

unpivot

( colnames for Vals in ([1],[2],[3],[4],[5])) as unpivot_handle

)

select

max ( case when colname ='Type' Then colnames END) AS Type,

max ( case when colname ='Path' Then colnames END) AS Path,

max ( case when colname ='Option1' Then colnames END) AS OPtion1,

max ( case when colname ='Option2' Then colnames END) AS Option2,

max ( case when colname ='Option3' Then colnames END) AS Option3

from unpivoted_data

group by Vals

Sk1ppy SSC Eights! Points: 810 More actions · Answer 2

That seems to work well. However, what if the number of items changes? This would get the information for the first 5 (or less) items, but what if there are 100 (could be any number at any time)?

matzer SSCrazy Eights Points: 9727 More actions · Answer 3

This one deals with a variable number of <Value> elements.

SELECT

[Type], [Path], [Option1], [Option2], [Option3]

FROM

(

SELECT

D.ID,

X.C.value('@Name[1]','NVARCHAR(2000)') AS [Option],

R.V.value('.', 'NVARCHAR(2000)') [Value],

ROW_NUMBER() OVER (PARTITION BY D.ID, X.C.value('@Name[1]','NVARCHAR(2000)') ORDER BY (SELECT 0)) RN

FROM

#XMLdata D

CROSS APPLY

D.data.nodes('//RegMultiStringLists/RegMultiStringList') X(C)

CROSS APPLY

X.C.nodes('Value') R(V)

) E

PIVOT

(

MAX([Value]) FOR [Option] IN ([Type], [Path], [Option1], [Option2], [Option3])

) AS PT;

Test setup:

CREATE TABLE #XMLdata

(

ID INT IDENTITY PRIMARY KEY,

data XML NOT NULL

)

GO

INSERT INTO #XMLdata (data) VALUES (

'<RegMultiStringLists>

<RegMultiStringList Name="Type" KeyPath="" KeyFlag="0">

<Value>msdrm.dll</Value>

<Value>CIAgent.DLL</Value>

<Value>CIStore.DLL</Value>

<Value>flash*.ocx</Value>

<Value>abc.dll</Value>

<Value>xyz.ocx</Value>

</RegMultiStringList>

<RegMultiStringList Name="Path" KeyPath="" KeyFlag="0">

<Value>%windir%\system32\</Value>

<Value>%windir%\SysWOW64\CCM\</Value>

<Value>%windir%\system32\CCM\</Value>

<Value>%windir%\syswow64\Macromed\Flash\</Value>

<Value>%windir%\system32\Macromed\Flash\</Value>

<Value>%windir%\system32\abc</Value>

<Value>%windir%\system32\xyz</Value>

</RegMultiStringList>

<RegMultiStringList Name="Option1" KeyPath="" KeyFlag="0">

<Value>false</Value>

<Value>true</Value>

<Value>false</Value>

<Value>true</Value>

</RegMultiStringList>

<RegMultiStringList Name="Option2" KeyPath="" KeyFlag="0">

<Value>true</Value>

<Value>false</Value>

</RegMultiStringList>

<RegMultiStringList Name="Option3" KeyPath="" KeyFlag="0">

<Value>false</Value>

</RegMultiStringList>

</RegMultiStringLists>')

GO

SELECT

[Type], [Path], [Option1], [Option2], [Option3]

FROM

(

SELECT

D.ID,

X.C.value('@Name[1]','NVARCHAR(2000)') AS [Option],

R.V.value('.', 'NVARCHAR(2000)') [Value],

ROW_NUMBER() OVER (PARTITION BY D.ID, X.C.value('@Name[1]','NVARCHAR(2000)') ORDER BY (SELECT 0)) RN

FROM

#XMLdata D

CROSS APPLY

D.data.nodes('//RegMultiStringLists/RegMultiStringList') X(C)

CROSS APPLY

X.C.nodes('Value') R(V)

) E

PIVOT

(

MAX([Value]) FOR [Option] IN ([Type], [Path], [Option1], [Option2], [Option3])

) AS PT;

GO

DROP TABLE #XMLdata

Sk1ppy SSC Eights! Points: 810 More actions · Answer 4

Wow, thanks Peter! That works perfectly!

Now, can you help me understand it? 🙂 I hate just getting the answer...I'd like to understand it well enough that I can write my own queries using the information rather than having to create a new post hoping that someone is smart enough and kind enough to answer for me.

So, I've updated what you sent along a little so that it reflects my real world scenario. Could you please step through the xml query portions to help me understand how it is working and how to apply the principles for new queries?

SELECT [Row Identifier]

,[Type]

,[Path]

,[Option1]

,[Option2]

,[Option3]

FROM (

SELECT tbl.[Row Identifier]

,X.C.value('@Name[1]','nvarchar(2000)') AS [Option]

,R.V.value('.', 'nvarchar(2000)') [Value]

,ROW_NUMBER() OVER (PARTITION BY tbl.[Row Identifier], X.C.value('@Name[1]','nvarchar(2000)') ORDER BY (SELECT 0)) RN

FROM [Table Name] tbl

CROSS APPLY tbl.[XML Column].nodes('//RegMultiStringLists/RegMultiStringList') X(C)

CROSS APPLY X.C.nodes('Value') R(V)

) E

PIVOT (

MAX([Value]) FOR [Option] IN ([Type], [Path], [Option1], [Option2], [Option3])

GO

Thank you!

matzer SSCrazy Eights Points: 9727 More actions · Answer 5

Glad it works out for you. I'm about to leave for work, so I don't have the time to give you an explanation right now. I'll be back.

Peter

matzer SSCrazy Eights Points: 9727 More actions · Answer 6

Ok, here's some explanation. Most of the work is done in the derived table, so let's rebuild it.

SELECT

D.ID,

X.C.value('@Name[1]','NVARCHAR(2000)') AS [Option]

FROM

#XMLdata D

CROSS APPLY

D.data.nodes('//RegMultiStringLists/RegMultiStringList') X(C)

The XML nodes() method, applied to the XML column data, generates a row for each occurence of the <RegMultiStringList> element in the XML column data by using the XQuery expression ('//RegMultiStringLists/RegMultiStringList' , resulting in data set of 5 rows with a single column containing the <RegMultiStringList> element. This data set has to be aliased (X) as well as the single column it contains (C). These rows are joined with the original row in #XMLdata. Now you can obtain the [Option] column by applying the XML value() method to the column X.C using the XQuery '@Name[1]'. The result will be converted to NVARCHAR(2000). Intermediate result:

ID Option

----------- -----------

1 Type

1 Path

1 Option1

1 Option2

1 Option3

Now we have to get the values.

SELECT

D.ID,

ROW_NUMBER() OVER (PARTITION BY D.ID, X.C.value('@Name[1]','NVARCHAR(2000)') ORDER BY (SELECT 0)) RN,

X.C.value('@Name[1]','NVARCHAR(2000)') AS [Option],

R.V.value('.', 'NVARCHAR(2000)') [Value]

FROM

#XMLdata D

CROSS APPLY

D.data.nodes('//RegMultiStringLists/RegMultiStringList') X(C)

CROSS APPLY

X.C.nodes('Value') R(V)

This time we use the nodes() method to get a dataset of all <Value> elements within the column X.C we generated in the previous query aliased as R(V). Using the value() method we can contain the value of the element and convert it to NVARCHAR(2000) (R.V..value('.', 'NVARCHAR(2000)'). Also we number each occurence of the <Value> element per ID using the ROW_NUMBER() function.

Intermediate result:

ID RN Option Value

--------- ---------------- --------------- ------

1 1 Option1 false

1 2 Option1 true

1 3 Option1 true

1 4 Option1 true

1 5 Option1 true

1 6 Option1 false

1 7 Option1 true

1 1 Option2 true

1 2 Option2 true

1 3 Option2 true

1 4 Option2 true

1 5 Option2 true

1 6 Option2 true

1 7 Option2 false

1 1 Option3 false

1 2 Option3 false

1 3 Option3 false

1 4 Option3 false

1 5 Option3 false

1 6 Option3 false

1 7 Option3 false

1 1 Path %windir%\system321 2 Path %windir%\SysWOW64\CCM1 3 Path %windir%\system32\CCM1 4 Path %windir%\syswow64\Macromed\Flash1 5 Path %windir%\system32\Macromed\Flash1 6 Path %windir%\system32\abc

1 7 Path %windir%\system32\xyz

1 1 Type msdrm.dll

1 2 Type CIAgent.DLL

1 3 Type CIStore.DLL

1 4 Type flash*.ocx

1 5 Type flash*.ocx

1 6 Type abc.dll

1 7 Type xyz.ocx

Now we have all the data we want in a regular dataset. The last thing to do is build a row with all distinct options per ID and row number. You can use PIVOT for that.

PIVOT

(

MAX([Value]) FOR [Option] IN ([Type], [Path], [Option1], [Option2], [Option3])

) AS PT

Hopefully it's a little bit more clear now.

Peter