如何從SQLServer2019(v15)中的XML中提取屬性值？

我需要將此 XML 中的元素提取為表格形式，但我似乎無法理解這將如何通過 XQuery 之類的東西在 SQL Server 上作業。

我在一個名為“#1”的臨時表中擁有所有資料，而 XML 本身位于該臨時表中名為“Message”的欄位中。如何將值“Test1”和“2,2 %”分別提取到名為“W08003”和“W1A081”的單獨欄位中？屬性名稱和架構會隨著時間的推移保持不變嗎？我還需要為當前臨時表中的每個 XML 逐行執行此操作。

<Individual xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Content>
    <status xmlns:d3p1="http://www.uc.se/schemas/ucOrderReply/" xmlns="http://www.uc.se/schemas/ucOrderReply/" d3p1:result="ok" />
    <uc xmlns="http://www.uc.se/schemas/ucOrderReply/">
      <xmlReply>
        <reports xmlns:d5p1="http://www.uc.se/schemas/ucOrderReply/" d5p1:lang="eng">
          <d5p1:report d5p1:id="7605089247" d5p1:name="Test1 Test2" d5p1:styp="K39" d5p1:index="0">
            <d5p1:group d5p1:id="W080" d5p1:index="0" d5p1:key="" d5p1:name="ID particulars">
              <d5p1:term d5p1:id="W08001">9760508923</d5p1:term>
              <d5p1:term d5p1:id="W08002">7605089277</d5p1:term>
              <d5p1:term d5p1:id="W08003">Test1</d5p1:term>
              <d5p1:term d5p1:id="W08004">Test2</d5p1:term>
            </d5p1:group>
            <d5p1:group d5p1:id="W1A0" d5p1:index="0" d5p1:key="" d5p1:name="UC RPB">
              <d5p1:term d5p1:id="W1A003">000000000000000022</d5p1:term>
              <d5p1:term d5p1:id="W1A081">2,2 %</d5p1:term>
              <d5p1:term d5p1:id="W1A082">2,18839</d5p1:term>
            </d5p1:group>
          </d5p1:report>
        </reports>
      </xmlReply>
    </uc>
  </Content>
</Individual>

當前 SQL 代碼：

WITH XMLNAMESPACES('http://www.uc.se/schemas/ucOrderReply/' AS ns,'http://www.uc.se/schemas/ucOrderReply/' AS d5p1)
SELECT ok.*
    ,X.g.value('(@d5p1:id)','varchar(20)') AS id
    ,X.g.value('(text())[1]','varchar(20)') AS term
into #2
FROM #1 as ok
CROSS APPLY(ok.[Message].nodes('Individual/Content/ns:uc/ns:xmlReply/ns:reports/ns:report/ns:group/ns:term') X(g)

uj5u.com熱心網友回復：

沒有預期的結果，也許這足以讓你開始。

由于您僅在到達 時才定義默認命名 空間，因此status您不能在 中使用DEFAULT命名空間XMLNAMESPACES，因此我將其命名ns并參考它。這為您提供了所有terms 及其id屬性的值：

DECLARE @XML xml = '<Individual xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Content>
        <status xmlns:d3p1="http://www.uc.se/schemas/ucOrderReply/" xmlns="http://www.uc.se/schemas/ucOrderReply/" d3p1:result="ok" />
        <uc xmlns="http://www.uc.se/schemas/ucOrderReply/">
            <xmlReply>
                <reports xmlns:d5p1="http://www.uc.se/schemas/ucOrderReply/" d5p1:lang="eng">
                    <report d5p1:id="7605089247" d5p1:name="Test1 Test2" d5p1:styp="K39" d5p1:index="0">
                        <group d5p1:id="W080" d5p1:index="0" d5p1:key="" d5p1:name="ID particulars">
                            <term d5p1:id="W08001">9760508923</term>
                            <term d5p1:id="W08002">7605089277</term>
                            <term d5p1:id="W08003">Test1</term>
                            <term d5p1:id="W08004">Test2</term>
                        </group>
                        <group d5p1:id="W1A0" d5p1:index="0" d5p1:key="" d5p1:name="UC RPB">
                            <term d5p1:id="W1A003">000000000000000022</term>
                            <term d5p1:id="W1A081">2,2 %</term>
                            <term d5p1:id="W1A082">2,18839</term>
                        </group>
                    </report>
                </reports>
            </xmlReply>
        </uc>
    </Content>
</Individual>';
WITH XMLNAMESPACES('http://www.uc.se/schemas/ucOrderReply/' AS ns,'http://www.uc.se/schemas/ucOrderReply/' AS d5p1)
SELECT X.g.value('(@d5p1:id)','varchar(20)') AS id,
       X.g.value('(text())[1]','varchar(20)') AS term
FROM @XML.nodes('Individual/Content/ns:uc/ns:xmlReply/ns:reports/ns:report/ns:group/ns:term') X(g);

我注意到自我用來撰寫此答案的初始版本以來，XML 已更改。該答案尚未（閱讀“不會”）為此進行調整。

標籤：

上一篇：SQLServer按重疊10天間隔分組

下一篇：將多行壓縮為每個鍵