Select all child nodes

Question

Post reply

Select all child nodes

farax_x

Hall of Fame

Points: 3579
More actions
October 6, 2016 at 7:28 am

#330733

Hi,
I have table like this :
DECLARE @t TABLE
(
Id INT ,
XmlData XML
);
INSERT INTO @t
(1, XmlData )
VALUES ( '<News>
<nNewsID>82172275</nNewsID>
<nPubDate>2016-08-02T07:27:21</nPubDate>
<nHeadline />
<imgPath>1395\13950512\82172275\82172275-70907707.jpg</imgPath>
<Photos>
<Photo>
<PhotoTitle />
<fullname>http://img.com/1395/13950512/82172275/82172275-70907707.jpg</fullname>
<IsPrimary>1</IsPrimary>
<filename>82172275-70907707.jpg</filename>
</Photo>
</Photos>
</News>' ),
(2, '<News>
<nNewsID>82172274</nNewsID>
<nPubDate>2016-08-02T07:27:24</nPubDate>
<imgPath>1395\13950512\82172274\82172274-70907705.jpg</imgPath>
<Photos>
<Photo>
<PhotoTitle />
<fullname>http://img.com/1395/13950512/82172274/82172274-70907704.jpg</fullname>
<IsPrimary>1</IsPrimary>
<filename>82172274-70907704.jpg</filename>
</Photo>
<Photo>
<PhotoTitle />
<fullname>http://img.com/1395/13950512/82172274/82172274-70907705.jpg</fullname>
<IsPrimary>0</IsPrimary>
<filename>82172274-70907705.jpg</filename>
</Photo>
</Photos>
</News>' ),
( 3, '<News>
<nNewsID>82172140</nNewsID>
<nPubDate>2016-08-02T05:58:24</nPubDate>
<imgPath>1395\13950512\82172140\82172140-70907550.jpg</imgPath>
<Photos>
<Photo>
<PhotoTitle />
<fullname>http://img.com/1395/13950512/82172140/82172140-70907546.jpg</fullname>
<IsPrimary>1</IsPrimary>
<filename>82172140-70907546.jpg</filename>
</Photo>
<Photo>
<PhotoTitle />
<fullname>http://img.com/1395/13950512/82172140/82172140-70907547.jpg</fullname>
<IsPrimary>0</IsPrimary>
<filename>82172140-70907547.jpg</filename>
</Photo>
<Photo>
<PhotoTitle />
<fullname>http://img.com/1395/13950512/82172140/82172140-70907548.jpg</fullname>
<IsPrimary>0</IsPrimary>
<filename>82172140-70907548.jpg</filename>
</Photo>
<Photo>
<PhotoTitle />
<fullname>http://img.com/1395/13950512/82172140/82172140-70907549.jpg</fullname>
<IsPrimary>0</IsPrimary>
<filename>82172140-70907549.jpg</filename>
</Photo>
<Photo>
<PhotoTitle />
<fullname>http://img.com/1395/13950512/82172140/82172140-70907550.jpg</fullname>
<IsPrimary>0</IsPrimary>
<filename>82172140-70907550.jpg</filename>
</Photo>
</Photos>
</News>' );
I want to select all fullnames :
Id fullname
--------------------------------
1 http://img.com/1395/13950512/82172275/82172275-70907707.jpg
2 http://img.com/1395/13950512/82172274/82172274-70907704.jpg
2 http://img.com/1395/13950512/82172274/82172274-70907705.jpg
3 http://img.com/1395/13950512/82172140/82172140-70907546.jpg
3 http://img.com/1395/13950512/82172140/82172140-70907547.jpg
3 http://img.com/1395/13950512/82172140/82172140-70907548.jpg
3 http://img.com/1395/13950512/82172140/82172140-70907549.jpg
3 http://img.com/1395/13950512/82172140/82172140-70907550.jpg
how its possible ?
this query wont work ! :ermm:
SELECT Id ,
XmlCol.query('./Photo/fullname').value('.', 'nvarchar(max)') AS 'fullname'
FROM @t t
CROSS APPLY t.XmlData.nodes('/News//Photos') x ( XmlCol );
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 1

SELECT Id ,

XmlCol.query('./fullname').value('.', 'nvarchar(max)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('/News//Photos/Photo') x ( XmlCol );

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 2

Try it like this...

SELECT Id ,

XmlCol.query('./fullname').value('.', 'nvarchar(max)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('//Photo') x ( XmlCol );

Results...

Id fullname

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

1 http://img.com/1395/13950512/82172275/82172275-70907707.jpg

2 http://img.com/1395/13950512/82172274/82172274-70907704.jpg

2 http://img.com/1395/13950512/82172274/82172274-70907705.jpg

3 http://img.com/1395/13950512/82172140/82172140-70907546.jpg

3 http://img.com/1395/13950512/82172140/82172140-70907547.jpg

3 http://img.com/1395/13950512/82172140/82172140-70907548.jpg

3 http://img.com/1395/13950512/82172140/82172140-70907549.jpg

3 http://img.com/1395/13950512/82172140/82172140-70907550.jpg

Edit... DOH! Too slow!

Alan Burstein SSC Guru Points: 61142 More actions · Answer 3

No need to use the Query method, it would be more efficient to just use value like this:

SELECT Id ,

XmlCol.value('(text())[1]', 'nvarchar(1000)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('//Photo/fullname') x ( XmlCol );

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

Alan.B (10/6/2016)
No need to use the Query method, it would be more efficient to just use value like this:
SELECT Id ,
XmlCol.value('(text())[1]', 'nvarchar(1000)') AS 'fullname'
FROM @t t
CROSS APPLY t.XmlData.nodes('//Photo/fullname') x ( XmlCol );

Curious... if there's more than one full name in the XML for a given ID (for example), how would you shred and return those?

Sorry about the ignorant question but, when it comes to XML code, I'm most definitely ignorant.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61142 More actions · Answer 5

Jeff Moden (10/6/2016)
Alan.B (10/6/2016)
No need to use the Query method, it would be more efficient to just use value like this:
SELECT Id ,
XmlCol.value('(text())[1]', 'nvarchar(1000)') AS 'fullname'
FROM @t t
CROSS APPLY t.XmlData.nodes('//Photo/fullname') x ( XmlCol );
Curious... if there's more than one full name in the XML for a given ID (for example), how would you shred and return those?

Interestingly, the way I wrote it would would successfully return all of the fullnames if there were more than for a specific ID and the fullnames were children of Photo. E.g. the data looked like this:

<Photo>

<fullname>http://img.com/1395/13950512/82172140/82172140-70907549.jpg</fullname>

<fullname>http://img.com/1395/13950512/82172140/82172140-70907549_1.jpg</fullname>

<fullname>http://img.com/1395/13950512/82172140/82172140-70907549_2.jpg</fullname>

<fullname>http://img.com/1395/13950512/82172140/82172140-70907549_3.jpg</fullname>

</Photo>

Below is an updated version of the sample data where this is the case along with a cleaned up version of my solution that you can test.

DECLARE @t TABLE

(

Id INT ,

XmlData XML

);

INSERT INTO @t

(Id, XmlData )

VALUES ( 1,'<News>

<nNewsID>82172275</nNewsID>

<nPubDate>2016-08-02T07:27:21</nPubDate>

<nHeadline />

<imgPath>1395\13950512\82172275\82172275-70907707.jpg</imgPath>

<Photos>

<Photo>

<PhotoTitle />

<fullname>http://img.com/1395/13950512/82172275/82172275-70907707.jpg</fullname>

<IsPrimary>1</IsPrimary>

<filename>82172275-70907707.jpg</filename>

</Photo>

</Photos>

</News>' ),

( 2, '<News>

<nNewsID>82172140</nNewsID>

<nPubDate>2016-08-02T05:58:24</nPubDate>

<imgPath>1395\13950512\82172140\82172140-70907550.jpg</imgPath>

<Photos>

<Photo>

<PhotoTitle />

<fullname>http://img.com/1395/13950512/82172140/82172140-70907546.jpg</fullname>

<fullname>http://img.com/1395/13950512/82172140/82172140-70907546xxx.jpg</fullname>

<IsPrimary>1</IsPrimary>

<filename>82172140-70907546.jpg</filename>

</Photo>

<Photo>

<PhotoTitle />

<fullname>http://img.com/1395/13950512/82172140/82172140-70907548.jpg</fullname>

<IsPrimary>0</IsPrimary>

<filename>82172140-70907548.jpg</filename>

</Photo>

<Photo>

<PhotoTitle />

<fullname>http://img.com/1395/13950512/82172140/82172140-70907549.jpg</fullname>

<fullname>http://img.com/1395/13950512/82172140/82172140-70907549_1.jpg</fullname>

<fullname>http://img.com/1395/13950512/82172140/82172140-70907549_2.jpg</fullname>

<fullname>http://img.com/1395/13950512/82172140/82172140-70907549_3.jpg</fullname>

<IsPrimary>0</IsPrimary>

<filename>82172140-70907549.jpg</filename>

</Photo>

</Photos>

</News>' );

SELECT Id,

f.names.value('(text())[1]', 'nvarchar(1000)') AS fullname

FROM @t t

CROSS APPLY t.XmlData.nodes('//Photo/fullname') f (names);

Looking at this solution: the nodes method is a SQLXML "iterator" that looks through the XML and returns each node as a separate record. In this case, for each ID in the table it's returning the text for every instance of //Photo/fullaname (every fullname element that is a child of a Photo element).

Now let's say that (for reasons I'll skip for now) that you had to write the CROSS APPLY statement like this:

CROSS APPLY t.XmlData.nodes('//Photo') p(photo) (like the other posters did). In that case you would need a second CROSS APPLY like so:

SELECT Id,

f.names.value('(text())[1]', 'nvarchar(1000)') AS fullName

FROM @t t

CROSS APPLY t.XmlData.nodes('//Photo') p(photo)

CROSS APPLY p.photo.nodes('fullname') f(names);

This was hastily prepared answer, I have a meeting in 1 minute. Hope this clears things up a little. If not, let me know and I'll be back online in a few hours.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Outstanding. Thanks for the tutorial, Alan. I might learn XML yet. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 7

Quick thought, no need to use the traversing operator "//" in the nodes path statement, it can be costly if the XML is complex.

😎

Non traversing examples

SELECT Id ,

XmlCol.value('(text())[1]', 'nvarchar(1000)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('News/Photos/Photo/fullname') x ( XmlCol );

SELECT Id ,

XmlCol.value('(fullname/text())[1]', 'nvarchar(1000)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('News/Photos/Photo') x ( XmlCol );

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 8

For completeness, 5 different versions which all produce the same execution plan although performance may vary depending on the XML structure.

😎

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

-- #1 Full traverse to the end node

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

SELECT Id ,

XmlCol.value('(text())[1]', 'nvarchar(100)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('//fullname') x ( XmlCol );

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

-- #2 Full path to the end node

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

SELECT Id ,

XmlCol.value('(text())[1]', 'nvarchar(100)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('News/Photos/Photo/fullname') x ( XmlCol );

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

-- #3 Partial path to the parent of the end node

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

SELECT Id ,

XmlCol.value('(fullname/text())[1]', 'nvarchar(100)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('News/Photos/Photo') x ( XmlCol );

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

-- #4 Traverse to the parent and the end node combination

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

SELECT Id ,

XmlCol.value('(text())[1]', 'nvarchar(100)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('//Photo/fullname') x ( XmlCol );

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

-- #5 FLOWR traverse to the end node

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

SELECT Id ,

XmlCol.value('(text())[1]', 'nvarchar(100)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('for $X in News/Photos/Photo/fullname

return $X') x ( XmlCol );

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

Eirikur Eiriksson (10/8/2016)
For completeness, 5 different versions which all produce the same execution plan although performance may vary depending on the XML structure.
😎

One more variant for these which shows a very marginal estimated cost gain over using the text() node in the .value method.

SELECT Id ,

XmlCol.value('.', 'nvarchar(100)') AS 'fullname'

FROM @t t

CROSS APPLY t.XmlData.nodes('News/Photos/Photo/fullname/text()') x ( XmlCol );

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 10

mister.magoo (10/8/2016)
Eirikur Eiriksson (10/8/2016)
For completeness, 5 different versions which all produce the same execution plan although performance may vary depending on the XML structure.
😎
One more variant for these which shows a very marginal estimated cost gain over using the text() node in the .value method.
SELECT Id ,
XmlCol.value('.', 'nvarchar(100)') AS 'fullname'
FROM @t t
CROSS APPLY t.XmlData.nodes('News/Photos/Photo/fullname/text()') x ( XmlCol );

Brilliant MM, thanks, I had totally missed that one.

😎

The difference between using the text function within the value method and the nodes method on the XML data type is quite substantial. In simple terms, without the text function in the nodes method, the nodes return value is an XML snipped containing the value and attributes if any. This requires the serializer and the hierarchical structure element (OrdPath) for reassembling the output value and in addition both the singleton notation within the value function and an enumeration of the output to match the singleton's value. In this case, the XML relational operator will always map to a nested loop join in the execution plan.

Using the text() function within the nodes method avoids the serialization and the OrdPath sorting as the nodes method's output is a scalar value. This enables the XML relational operator to be mapped to a left outer merge join and eliminates few other operators from the execution plan.

Another way of achieving the same kind of short-cut is to use the data() function within the value method, the query below will produce the exactly same plan as the one with the text() function in the nodes method.

~~SELECT~~

~~XmlCol.value('data(.)','nvarchar(max)')~~

~~FROM @TXML.nodes('News/Photos/Photo/fullname/text()') x ( XmlCol );~~

Edit: Typo, crossed over as not correct.