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 ha开发者_JS百科pen.

any ideas?

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)