CODEWITHSUNDEEP
t-sqlsql-server-2012xquery
QuietLeni
QuietLeni

Reputation: 137

How do I get the contents of all of the nodes in a root node with SQL and XQuery?

I have the following table structure:

CREATE TABLE   SpecialTable
(
      Key      UNIQUEIDENTIFIER,
      XMLField VARCHAR(MAX)
)

In the first tuple:

Key = "28384841-17283848-7836-18292939"
XMLField =
"<RootNode>
     <ForeignKey>92383829-27374848-1298-19283789</ForeignKey>
     <ForeignKey>47585853-27374848-4759-19283939</ForeignKey>
     <ForeignKey>37383829-27374848-3747-19283930</ForeignKey>
</RootNode>"

In another tuple, I see:

Key = "89984841-17283848-7836-18292939"
XMLField =
"<RootNode>
      <ForeignKey>92383829-27374848-1298-19283789</ForeignKey>
      <ForeignKey>37383829-27374848-3747-19283930</ForeignKey>
</RootNode>"

In a further tuple, I see:

Key = "11114841-17283848-7836-18292939"
XMLField =
"<RootNode>
      <ForeignKey>37383829-27374848-3747-19283930</ForeignKey>
</RootNode>"

What I need to do is to get the following dataset out:

Key                                 ForeignKey
28384841-17283848-7836-18292939     92383829-27374848-1298-19283789
28384841-17283848-7836-18292939     47585853-27374848-4759-19283939
28384841-17283848-7836-18292939     37383829-27374848-3747-19283930
89984841-17283848-7836-18292939     92383829-27374848-1298-19283789
89984841-17283848-7836-18292939     37383829-27374848-3747-19283930
11114841-17283848-7836-18292939     37383829-27374848-3747-19283930

I must say that this is a simplified data set and that the data was more complex than this and I have got to a point where I cannot get any further.

I have tried this:

SELECT   sp.Key,
         x.XmlCol.Query('.')
FROM     SpecialTable AS sp
CROSS APPLY sp.XMLField.nodes('/RootNode') x(XmlCol)

However, it seems just to show the Key and the whole of the XML of the XMLField.

Also, I tried this:

SELECT   sp.Key,
         x.XmlCol.Query('ForeignKey[text]')
FROM     SpecialTable AS sp
CROSS APPLY sp.XMLField.nodes('/RootNode') x(XmlCol)

And I get only the first value in the first ForeignKey node and not the others.

What am I doing wrong?

Kindest regards,

QuietLeni

Upvotes: 0

Views: 70

Answers (1)

marc_s
marc_s

Reputation: 754973

First of all - if your data looks like XML, quacks like XML, smells like XML - then it IS XML and you should use the XML datatype to store it!

Also: be aware that Key is a very generic term, and also a T-SQL reserved keyword, so it makes for a really bad column name - use something more meaningful that doesn't clash with a keyword!

Once you've done that, you should be able to use this code to get your desired results:

SELECT 
    [Key],
    ForeignKey = xc.value('.', 'varchar(50)')
FROM
    dbo.SpecialTable
CROSS APPLY
    XMLField.nodes('/RootNode/ForeignKey') AS XT(XC)

This will only work if you column XMLField is of XML datatype !! (which it really should be anyway)

Upvotes: 1

Related Questions