Read attribute from XML in SSMS using XML.Node-CodePudding

I'm trying to read an XML file, the aim is to get the xmlns attribute which should return

ANSWER!!!

But as you can see below, I can't even get the Value column correct, I was expecting it to return "abc" but for some reason it's returning

123                             abc

Here's a simplified script to run in SSMS:

DECLARE @XML AS XML = '
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ObjectDefinition>
        <Database>
            <ID>White Stuff BI OLAP Solution</ID>
            <Dimensions>
                <Dimension>
                    <ID>Dim Dynamic Date Filter</ID>
                    <Annotations>
                        <Annotation>
                            <Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramLayout</Name>
                            <Value>
                                <dds xmlns="ANSWER!!!">123</dds>
                            abc</Value>
                        </Annotation>
                    </Annotations>
                </Dimension>
            </Dimensions>
        </Database>
    </ObjectDefinition>
</Create>'

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://schemas.microsoft.com/analysisservices/2003/engine'
)


SELECT 
    a.value('(../../../../ID)[1]', 'nvarchar(100)') as [First ID]
    ,a.value('(../../ID)[1]', 'nvarchar(100)') as [Second ID]
    ,a.value('(Name)[1]', 'nvarchar(1000)') as [Name]
    ,a.value('(Value)[1]', 'nvarchar(1000)') as [Value]
    ,a.value('(Value/dds)[1]', 'nvarchar(1000)') as [dds]
    ,a.value('(Value/dds/@xmlns)[1]', 'nvarchar(1000)') as [xmlns attribute]

FROM @XML.nodes('/Create/ObjectDefinition/Database/Dimensions/Dimension/Annotations/Annotation') as x1(a)

Result in SSMS:

CodePudding user response：

As @Larnu already mentioned, the XML doesn't look right.

Please try the following solution.

Notable points:

It is using two namespace declarations.
It is better NOT to traverse XML up.
../text() is added to XPath expressions for performance reasons.

SQL

DECLARE @xml AS XML = 
N'<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ObjectDefinition>
        <Database>
            <ID>White Stuff BI OLAP Solution</ID>
            <Dimensions>
                <Dimension>
                    <ID>Dim Dynamic Date Filter</ID>
                    <Annotations>
                        <Annotation>
                            <Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramLayout</Name>
                            <Value>
                                <dds xmlns="ANSWER!!!">123</dds>
                            abc</Value>
                        </Annotation>
                    </Annotations>
                </Dimension>
            </Dimensions>
        </Database>
    </ObjectDefinition>
</Create>';

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://schemas.microsoft.com/analysisservices/2003/engine',
    'ANSWER!!!' AS ns2
)
SELECT a.value('(ID/text())[1]', 'nvarchar(100)') as [First ID]
    ,a.value('(Dimensions/Dimension/ID/text())[1]', 'nvarchar(100)') as [Second ID]
    ,a.value('(Dimensions/Dimension/Annotations/Annotation/Name/text())[1]', 'nvarchar(1000)') as [Name]
    ,a.value('(Dimensions/Dimension/Annotations/Annotation/Value/ns2:dds/text())[1]', 'nvarchar(1000)') as [dds]
FROM @xml.nodes('/Create/ObjectDefinition/Database') as x1(a);

Output

 ------------------------------ ------------------------- ----------------------------------------------------------------------- ----- 
|           First ID           |        Second ID        |                                 Name                                  | dds |
 ------------------------------ ------------------------- ----------------------------------------------------------------------- ----- 
| White Stuff BI OLAP Solution | Dim Dynamic Date Filter | http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramLayout | 123 |
 ------------------------------ ------------------------- ----------------------------------------------------------------------- -----