xquery get node value

Question

I'm trying to get value of cat but it retuns me null. Please, help me to get this value. Thanks in advance!

DECLARE @xml XML 
SET @xml = '
<cat:catalog xmlns:cat="http://datypic.com/cat"
             xmlns:prod="http://datypic.com/prod">
  <cat:number>1446</cat:number>
  <prod:product>
    <prod:number>563</prod:number>
    <prod:name prod:language="en">Floppy Sun Hat</prod:name>
  </prod:product>
</cat:catalog>'

Select @xml.value('declare namespace ns="xmlns:cat=http://datypic.com/cat"; /ns:cat[1]/number[1]', 'int')

Brett Zamir · Accepted Answer · 2012-09-20T14:01:20.097

You are trying to grab "cat[1]" but there is no element "cat", it is only a prefix. You want "catalog" I believe. Also, you must namespace prefix the "number" child as well. And the way you wrote declare namespace is I believe incorrect as well. No need for "xmlns:cat=" in there.

In other words:

Select @xml.value('declare namespace ns="http://datypic.com/cat"; /ns:catalog[1]/ns:number[1]', 'int')

...ought to work. But podiluska's prefix change is more suitable since "ns" doesn't give any hint about what namespace you are using. The important thing to bear in mind is that the prefix is arbitrary (it could be anything), but you have to tie it to whatever element you want in that namespace.

Thanks for reply! I want to get cat : number value. Well, I replace the last line with follow Select @xml.value('/catalog[1]/cat[1]', 'int') but stil I get null. — JohnyMotorhead, Sep 20 '12 at 13:40

score 3 · Answer 2 · answered Sep 20 '12 at 13:57

3

Try

Select @xml.value('declare namespace cat="http://datypic.com/cat"; 
    (/cat:catalog/cat:number)[1]','int')

answered Sep 20 '12 at 13:57

podiluska

50,950
7
98
104

score 1 · Answer 3 · answered Sep 20 '12 at 23:58

You can also do this using WITH XMLNAMESPACES, which makes the namespace declarations apply across the whole statement, rather than a single call to .value. This is useful if you need to get multiple values from the XML, eg

DECLARE @xml XML  
SET @xml = '<cat:catalog xmlns:cat="http://datypic.com/cat" xmlns:prod="http://datypic.com/prod">
  <cat:number>1446</cat:number>
  <prod:product>
    <prod:number>563</prod:number>
    <prod:name prod:language="en">Floppy Sun Hat</prod:name>
  </prod:product>
</cat:catalog>' 

;WITH XMLNAMESPACES (
'http://datypic.com/cat' AS cat,
'http://datypic.com/prod' AS prod
)
SELECT 
    c.c.value('(cat:number/text())[1]', 'INT') 'cat:number',
    p.c.value('(prod:number/text())[1]', 'INT') 'prod:number',
    p.c.value('(prod:name/@prod:language)[1]', 'VARCHAR(2)') 'prod:language',
    p.c.value('(prod:name/text())[1]', 'VARCHAR(50)') 'prod:name'
FROM @xml.nodes('cat:catalog') c(c)
    CROSS APPLY c.c.nodes('prod:product') p(c)

xquery get node value

3 Answers3