使用XML.Node從SSMS中的XML讀取屬性

我正在嘗試讀取 XML 檔案，目的是讓 ddscontrol 回傳

888

在一份記錄中和

999

在另一個。

同樣對于工具提示，我需要回傳

TTT111

和

TTT222

盡管出于某種原因，我似乎無法更深入地超越 dds，這是一個在 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)

SSMS 中的結果：

uj5u.com熱心網友回復：

正如@Larnu 已經提到的那樣，XML 看起來不正確。

請嘗試以下解決方案。

值得注意的點：

• 它使用兩個命名空間宣告。
• 最好不要向上遍歷 XML。
• ../text()出于性能原因被添加到 XPath 運算式中。

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

標籤：xml 短信 xml节点

上一篇：如何使用PowerShell僅讀取一次來更新XML？

下一篇：XSLT樣式表中具有相同匹配的多個模板？