Read attribute from XML in SSMS using XML.Node

I'm trying to read an XML file, the aim is to get ddscontrol to return

in one record and

in the other.

Similarly for tooltip I need to return

TTT111

and

TTT222

I can't seem to go deeper beyond dds though for some reason, 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="this is an XML namespace">123
                                    <ddscontrol tooltip="TTT111">888</ddscontrol></dds>
                            abc</Value>
                        </Annotation>
                        <Annotation>
                            <Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramLayout</Name>
                            <Value>
                                <dds xmlns="this is an XML namespace">456
                                    <ddscontrol tooltip="TTT222">999</ddscontrol></dds>
                            abc</Value>
                        </Annotation>
                    </Annotations>
                </Dimension>
            </Dimensions>
        </Database>
    </ObjectDefinition>
</Create>'

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://schemas.microsoft.com/analysisservices/2003/engine',
    'this is an XML namespace' AS ns2
                      
)


SELECT 
    a.value('(../../../../ID/text())[1]', 'nvarchar(100)') as [First ID]
    ,a.value('(../../ID/text())[1]', 'nvarchar(100)') as [Second ID]
    ,a.value('(Name/text())[1]', 'nvarchar(1000)') as [Name]
    ,a.value('(Value/text())[1]', 'nvarchar(1000)') as [Value]
    ,a.value('(Value/ns2:dds/text())[1]', 'nvarchar(1000)') as [dds]
    ,a.value('(Value/ns2:dds/ddscontrol/text())[1]', 'nvarchar(1000)') as [ddscontrol]
    ,a.value('(Value/ns2:dds/ddscontrol/@tooltip)[1]', 'nvarchar(1000)') as [tooltip]

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

Result in SSMS:

Solution

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="this is an XML namespace">123
                                    <ddscontrol tooltip="TTT111">888</ddscontrol>
                                </dds>abc</Value>
                        </Annotation>
                        <Annotation>
                            <Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramLayout</Name>
                            <Value>
                                <dds xmlns="this is an XML namespace">456
                                    <ddscontrol tooltip="TTT222">999</ddscontrol>
                                </dds>abc</Value>
                        </Annotation>
                    </Annotations>
                </Dimension>
            </Dimensions>
        </Database>
    </ObjectDefinition>
</Create>';

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://schemas.microsoft.com/analysisservices/2003/engine',
    'this is an XML namespace' 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]
    ,b.value('(Name/text())[1]', 'nvarchar(1000)') as [Name]
    ,b.value('(Value/ns2:dds/text())[1]', 'nvarchar(1000)') as [dds]
    ,b.value('(Value/ns2:dds/ns2:ddscontrol/text())[1]', 'nvarchar(1000)') as ddscontrol
    ,b.value('(Value/ns2:dds/ns2:ddscontrol/@tooltip)[1]', 'nvarchar(1000)') as tooltip
FROM @xml.nodes('/Create/ObjectDefinition/Database') as t1(a)
    CROSS APPLY t1.a.nodes('Dimensions/Dimension/Annotations/Annotation') AS t2(b);