SQL Server : XQUERY namespace parsing

Question

I have the following XML:

    declare @xml    xml = '
    <DiscoverResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
      <return>
        <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
          <row>
            <xars:METADATA xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
              <Server>
                <Name>myName</Name>
              </Server>
            </xars:METADATA>
          </row>
        </root>
       </return>
    </DiscoverResponse>'

And I have been able to parse up to the <row> tag with the following query

    ; WITH XMLNAMESPACES(
        'urn:schemas-microsoft-com:xml-analysis'                        AS ns1,
        'urn:schemas-microsoft-com:xml-analysis:rowset'                 AS ns2
    )
    select 
        t.x.value('ns2:Name[1]','varchar(100)') AS Name
    from @xml.nodes('//ns1:DiscoverResponse/ns1:return/ns2:root/ns2:row') t(x)

I am stuck trying to figure out how to query past the <xars:METADATA> tag. That is, I have not been able successfully construct a namespace declaration and path specification to get to the <Server> node (I am trying to extract the Name of the Server). I am running afoul of the extra colon it seems.

Thanks in advance.

Your XML is invalid (or maybe the one posted is incomplete). XML variable declaration + assignment triggered exception : Msg 9459, Level 16, State 1, Line 1. XML parsing: line 6, character 93, undeclared prefix — har07
– har07, Commented Jul 17, 2016 at 7:43
How is this XML generated? The existing answers are quite OK, but if the XML is really there as you've posted it, you are in troubles... There are several default namespaces, the namespace xars is not declared at all... Where does this come from? Is the generation under your control? There are ways to query this, but it will be ugly and slow... — Gottfried Lesigang
– Gottfried Lesigang, Commented Jul 17, 2016 at 20:49
Alas, it is the direct output of a SSAS XMLA query, so I have no control of whether the returned XML is properly formatted or not. — Jeff L
– Jeff L, Commented Jul 19, 2016 at 19:09

Phil Blackburn · Accepted Answer · 2016-07-17 08:53:35Z

You have a couple of things going wrong:

The xars namespace is not declared in the xml. I believe the namespace for xars is "urn:schemas-microsoft-com:xml-analysis:rowset" which you actually have as a default namespace (form the root) but not explicitly mapped to the xars prefix.
The xquery is missing the reference to namespace "http://schemas.microsoft.com/analysisservices/2003/engine" which in the xml is set as the default namespace from and including METADATA.

So first, I've altered your sample xml to include a namespace for xars: I expect your actual xml has this declared elsewhere, so you problably dont need to make this change.

declare @xml    xml = N'<DiscoverResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
  <return>
    <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
      <row>
        <xars:METADATA xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xars="urn:schemas-microsoft-com:xml-analysis:rowset">
          <Server>
            <Name>myName</Name>
          </Server>
        </xars:METADATA>
      </row>
    </root>
   </return>
</DiscoverResponse>'

Second, I've added the 'http://schemas.microsoft.com/analysisservices/2003/engine' (as ns3) to your query and change the query to reference it.

; WITH XMLNAMESPACES(
    'urn:schemas-microsoft-com:xml-analysis'                        AS ns1,
    'urn:schemas-microsoft-com:xml-analysis:rowset'                 AS ns2,
    'http://schemas.microsoft.com/analysisservices/2003/engine'     AS ns3
)
select 
    t.x.value('ns3:Name[1]','varchar(100)') AS Name
from @xml.nodes('//ns1:DiscoverResponse/ns1:return/ns2:root/ns2:row/ns2:METADATA/ns3:Server') t(x)

Collectives™ on Stack Overflow

SQL Server : XQUERY namespace parsing

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related