CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxpath
JJ.
JJ.

Reputation: 9950

How to extract specific value from XML in SQL?

<Term xmlns:xxxlocal="xx.xx.xx.xx" href="abcde?terms=1" policyTerm="1">
   <EffectiveDate>2012-05-22T00:00:00-04:00</EffectiveDate>
   <ExpirationDate>2013-05-22T00:00:00-04:00</ExpirationDate>
   <LocationCode>XXXXXX</LocationCode>
   <ProductRef href="products/XXXXXXXXXXXXXXXXXXXXX" idref="XXXXXXXXXXXXXXXXXXXXX" version="ZZZZZZZZZZZZZZZZZZZZZZZ">
       <CachedItem name="Label" value="I NEED TO EXTRACT THIS VALUE" />
    </ProductRef>
...

I'm trying to extract the VALUE for the Label1 under <ProductRef>.

I'm trying this but it is not working:

SELECT XmlDoc.value('(/Term/ProductRef[1]/CachedItem[@name=sql:variable("@Label")]/@value)[1]','varchar(100)')

I keep getting the error:

Msg 9501, Level 16, State 2, Line 1
XQuery: Unable to resolve sql:variable('@Label'). The variable must be declared as a scalar TSQL variable.

What am I doing wrong?

Upvotes: 1

Views: 758

Answers (1)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Try this

SELECT @xml.value('(/Term/ProductRef[1]/CachedItem/@value)[1]','varchar(100)')

Demo on SQLFiddle

Upvotes: 1

Related Questions