Parse XML using TSQL and XQuery - Get attribute value for a given element value

2
votes

I'm trying to shred a XML file into a mssql database (SQL Server 2008 R2). I'm looking to find the attribute value of a specific element value.

An excerpt of the file:

<company>
    <names>
        <name percent="6.55">Company X</name>
        <name percent="4.99">Company Y</name>
        <name percent="4.92">Company Z</name>
    </names>
</company>

So I'm looking for the value of the percent attribute of the name element where the name element equals "Company Z". Which should in this case return the value "4.92".

Thus far I've come up with the following code:

declare @xml xml

set @xml = N'
<company>
    <names>
        <name percent="6.55">Company X</name>
        <name percent="4.99">Company Y</name>
        <name percent="4.92">Company Z</name>
    </names>
</company>'

select
    @xml.value('(/company/names[name = "Company Z"]/@percent)[1]', 'decimal(8,5)') as Percentage

This returns NULL. I've tried several paths, but none of it is returning what I'm looking for. Can someone please point out what I'm doing wrong?

Any help is much appreciated!

2
tsqlxpathsql-server-2008-r2xquery

2 Answers

0
votes
declare @xml xml

set @xml = N'
<company>
    <names>
        <name percent="6.55">Company X</name>
        <name percent="4.99">Company Y</name>
        <name percent="4.92">Company Z</name>
    </names>
</company>'

select
    @xml.value('(/company/names/name[. = "Company Z"]/@percent)[1]', 'decimal(8,5)') as Percentage
0
votes

try

data(/company/names/name[.='Company Z']/@percent)

as your xpath