Microsoft T-SQL XML Parsing Issue

Question

I have XML similar to the following:

DECLARE @XML AS XML = 
'<RootElement xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubElement xmlns="http://foobar">
    <foo1>bar1</foo1>
  </SubElement>
</RootElement>'

And I'm trying to parse it with the following SQL:

; WITH XMLNAMESPACES(DEFAULT 'http://foobar')
SELECT 
    f.x.value('foo1[1]', 'varchar(10)')
from 
    @xml.nodes('/RootElement/SubElement') as f(x)

But it doesn't seem to work. Is the XML namespace on the SubElement node causing the issue? I ask because the following configuration works:

DECLARE @XML AS XML = 
'<RootElement xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubElement>
    <foo1>bar1</foo1>
  </SubElement>
</RootElement>'

SELECT 
    f.x.value('foo1[1]', 'varchar(10)')
from 
    @xml.nodes('/RootElement/SubElement') as f(x)

FYI:

select @@VERSION

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

By using the default XML namespace, you're applying that namespace to all nodes - but your root node <RootElement> is not in that namespace! You need to give your XML namespace an extension and then apply that XML namespace only to the <SubElement> tag (seem ljh's answer) — marc_s
– marc_s, Commented Mar 22, 2013 at 6:13

ljh · Accepted Answer · 2013-03-21 23:41:02Z

2

Try this:


; WITH XMLNAMESPACES('http://foobar' as Y)
SELECT 
    f.x.value('.', 'varchar(10)')
from 
    @xml.nodes('/RootElement/Y:SubElement') as f(x)

answered Mar 21, 2013 at 23:41

ljh

2,6341 gold badge16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Microsoft T-SQL XML Parsing Issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related