2

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

DECLARE @XML XML

SET @XML = 
'<NodeA xmlns="https://XYZ.xsd">
<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);

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

4

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

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);

produces

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.