Stuck on simple XML

Question

Stuck on simple XML

btio_3000

SSCrazy

Points: 2662
More actions
October 27, 2016 at 12:08 pm

#318473

declare @xml xml =
'<root>
<name>hand
<val>strong</val>
</name>
<name>mind
<val>beautiful</val>
</name>
</root>'
I need to return val for "hand", i.e. "strong" in this example
SELECT
x.task.value( '(./root[name = "hand"]/val/text())[1]', 'varchar(100)' )
FROM
@xml.nodes('.') AS x(task)
returns NULL though...
wrong path? syntax?
Thank you!

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

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

drew.allen SSC Guru Points: 76984 More actions · Answer 1

btio_3000 (10/27/2016)
declare @xml xml =
'<root>
<name>hand
<val>strong</val>
</name>
<name>mind
<val>beautiful</val>
</name>
</root>'
I need to return val for "hand", i.e. "strong" in this example
SELECT
x.task.value( '(./root[name = "hand"]/val/text())[1]', 'varchar(100)' )
FROM
@xml.nodes('.') AS x(task)
returns NULL though...
wrong path? syntax?
Thank you!

There are several issues here the primary one being that your xml has elements that contain both text and elements as direct children. The value of a node is the concatenated text of all of its descendant nodes. This means that the values of your name nodes are "handstrong" and "mindbeautiful", which is part of the reason that you can't find them.

The second issue is that your path is wrong. The path that you are using is /root/val/text. The name only specifies that the root node has a name element with the specified value, but it is not included in the path. What you want instead is '(./root/name[. = "hand"]/val/text())[1]'.

You probably also don't want to start with '.' as your node. It's an identity operation. It returns the exact same XML fragment as your original XML variable. The purpose of using nodes is to produce fragments from a starting point other than the original fragment.

Finally, the '.' at the beginning of your path indicates a relative path from the current node. Since your current node is the whole document, there is no difference here between './root' and just '/root' and the second one is a lot clearer that you are starting at the top of the document.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

DesNorton SSC-Insane Points: 24379 More actions · Answer 2

This works

declare @xml xml =

'<root>

<name>hand

<val>strong</val>

</name>

<name>mind

<val>beautiful</val>

</name>

</root>'

SELECT x.task.value( './val[1]', 'varchar(100)' )

FROM @xml.nodes('./root/name') AS x(task)

WHERE RTRIM(REPLACE(REPLACE(x.task.value( './text()[1]', 'varchar(100)' ), CHAR(13), ''), CHAR(10), '')) = 'hand'

However, if the xml were better formed ...

declare @xml xml =

'<root>

<name id="hand">

<val>strong</val>

</name>

<name id="mind">

<val>beautiful</val>

</name>

</root>'

SELECT

x.task.value( '(/root/name[@id="hand"]/val)[1]', 'varchar(100)' )

FROM @xml.nodes('.') AS x(task)

How to post data/code on a forum to get the best help.

drew.allen SSC Guru Points: 76984 More actions · Answer 3

Actually, XML uses Unix-style line terminators (LF) rather than Windows-style (CR/LF), so it's not necessary to test for CHAR(13) unless the XML document specifically contains the character encoding for CR ( ).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

btio_3000 SSCrazy Points: 2662 More actions · Answer 4

Thank you Drew, thank you DesNorton, I got it, it makes sense

DesNorton SSC-Insane Points: 24379 More actions · Answer 5

drew.allen (10/27/2016)
Actually, XML uses Unix-style line terminators (LF) rather than Windows-style (CR/LF), so it's not necessary to test for CHAR(13) unless the XML document specifically contains the character encoding for CR ( ).
Drew

Thanks Drew.

I never knew that.

How to post data/code on a forum to get the best help.