CODEWITHSUNDEEP
sqlxpathxquery
Harold Sota
Harold Sota

Reputation: 7566

xpath for xquery in SQL

I have a xml filed in sql table

the xml have the structure like these

<root>
<element>
<sub name="1">
<date>the date <date>
</sub>
<sub name="2">
<date>the date <date>
</sub>
<sub name="3">
<date>the date <date>
</sub>
.....
<element>
</root>

how i can get the date of the element with the name 1?

I have prove :

 /root/element/sub[Name = "1"]/date/text()

but nothing hapen.

any ideas?

Upvotes: 0

Views: 1636

Answers (1)

marc_s
marc_s

Reputation: 754268

Try this (SQL Server 2005 and up):

DECLARE @Input XML 
SET @input = '<root>
<element>
<sub name="1">
<date>the date</date>
</sub>
<sub name="2">
<date>the date</date>
</sub>
<sub name="3">
<date>the date</date>
</sub>
</element>
</root>'

SELECT 
    @input.value('(/root/element/sub[@name="1"]/date)[1]', 'varchar(50)')

You need to use @name to get the XML attribute name - without the @, it's trying to find an XML element (instead of the attribute)

Upvotes: 2

Related Questions