patternsqlMinor

Query XML without namespace

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

withoutquerynamespacexml

Problem

I can query XML, which has defined xmlns tag with common table expression

DECLARE @XML XML

SET @XML = 
'

    
    
  

'

;WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd')

    SELECT
        r.value('fn:local-name(.)', 'nvarchar(50)') as SectionName,
        r.value('@attrA','NVARCHAR(250)') attrA
    FROM    @XML.nodes('/NodeA/NodeB/*') AS t(r);

But what if source XML does not contain any xmlns tag ?
How can i workaround missing namespace ?

Solution

You can just remove the WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd') part:

DECLARE @XML XML

SET @XML = 
'

    
    
  

'

;--WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd')

    SELECT
        r.value('fn:local-name(.)', 'nvarchar(50)') as SectionName,
        r.value('@attrA','NVARCHAR(250)') attrA
    FROM    @XML.nodes('/NodeA/NodeB/*') AS t(r);

produces

+-------------+-------+
| SectionName | attrA |
+-------------+-------+
| NodeC1      | Hello |
| NodeC2      | World |
+-------------+-------+

Please note, WITH XMLNAMESPACES does not create a common table expression, it just declares an optional namespace

Code Snippets

DECLARE @XML XML

SET @XML = 
'<NodeA>
<NodeB verzePis="">
    <NodeC1 attrA="Hello" />
    <NodeC2 attrA="World" />
  </NodeB>
</NodeA>
'

;--WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd')

    SELECT
        r.value('fn:local-name(.)', 'nvarchar(50)') as SectionName,
        r.value('@attrA','NVARCHAR(250)') attrA
    FROM    @XML.nodes('/NodeA/NodeB/*') AS t(r);

+-------------+-------+
| SectionName | attrA |
+-------------+-------+
| NodeC1      | Hello |
| NodeC2      | World |
+-------------+-------+

Context

StackExchange Database Administrators Q#129083, answer score: 6

Revisions (0)

No revisions yet.