如何從名稱相同的XML中獲取值-有解無憂

我必須從 XML 檔案創建一個表。

問題是每個 ID 都可以有多個行。

所以表格需要看起來像這樣。

ID	ec_date	ec_description	ec_型別	ec_currency
54bd0b6f-693e-476f-a80e-0094de4cf3b4	2021 年 7 月 27 日	Reiskosten woon-werk	XP504	歐元
9adfe4dd-49be-48fb-a838-009e861be8bd	2021 年 8 月 24 日	雷斯科斯坦阿爾克馬爾-贊丹	XP502	歐元
9adfe4dd-49be-48fb-a838-009e861be8bd	2021 年 8 月 26 日	雷斯科斯坦阿爾克馬爾-贊丹	XP502	歐元

這是我的 XML 結構：

<Root>
  <Data>
    <Data>
      <table>
        <id>{54bd0b6f-693e-476f-a80e-0094de4cf3b4}</id>
        <rows>
          <row>
            <columns>
              <column name="ec_date" value="27-July-2021" type="System.DateTime" />
              <column name="ec_description" value="Reiskosten woon-werk" type="System.String" />
              <column name="ec_Type" value="XP504" type="System.String" />
              <column name="ec_currency" value="EUR" type="System.String" />
            </columns>
          </row>
        </rows>
        <key>DefaultKey</key>
        <total>145.14</total>
        <AddOnKey>0</AddOnKey>
        <data />
        <parameters />
      </table>
    </Data>
  </Data>
  <Data>
    <Data>
      <table>
        <id>{9adfe4dd-49be-48fb-a838-009e861be8bd}</id>
        <rows>
          <row>
            <columns>
              <column name="ec_date" value="24-August-2021" type="System.DateTime" />
              <column name="ec_description" value="reiskosten Alkmaar-Zaandam" type="System.String" />
              <column name="ec_Type" value="XP502" type="System.String" />
              <column name="ec_currency" value="EUR" type="System.String" />
            </columns>
          </row>
          <row>
            <columns>
              <column name="ec_date" value="26-August-2021" type="System.DateTime" />
              <column name="ec_description" value="reiskosten Alkmaar-Zaandam" type="System.String" />
              <column name="ec_Type" value="XP502" type="System.String" />
              <column name="ec_currency" value="EUR" type="System.String" />
            </columns>
          </row>
        </rows>
        <key>DefaultKey</key>
        <total>8.82</total>
        <AddOnKey>0</AddOnKey>
        <data />
        <parameters />
      </table>
    </Data>
  </Data>
 </Root>

到目前為止，我已經嘗試過這個查詢：

DECLARE @XMLData XML = ('<Root>
  <Data>
    <Data>
      <table>
        <id>{54bd0b6f-693e-476f-a80e-0094de4cf3b4}</id>
        <rows>
          <row>
            <columns>
              <column name="ec_date" value="27-July-2021" type="System.DateTime" />
              <column name="ec_description" value="Reiskosten woon-werk" type="System.String" />
              <column name="ec_Type" value="XP504" type="System.String" />
              <column name="ec_currency" value="EUR" type="System.String" />
            </columns>
          </row>
        </rows>
        <key>DefaultKey</key>
        <total>145.14</total>
        <AddOnKey>0</AddOnKey>
        <data />
        <parameters />
      </table>
    </Data>
  </Data>
  <Data>
    <Data>
      <table>
        <id>{9adfe4dd-49be-48fb-a838-009e861be8bd}</id>
        <rows>
          <row>
            <columns>
              <column name="ec_date" value="24-August-2021" type="System.DateTime" />
              <column name="ec_description" value="reiskosten Alkmaar-ZaandamXP502" type="System.String" />
              <column name="ec_Type" value="XP502" type="System.String" />
              <column name="ec_currency" value="EUR" type="System.String" />
            </columns>
          </row>
          <row>
            <columns>
              <column name="ec_date" value="26-August-2021" type="System.DateTime" />
              <column name="ec_description" value="reiskosten Alkmaar-Zaandam" type="System.String" />
              <column name="ec_Type" value="XP502" type="System.String" />
              <column name="ec_currency" value="EUR" type="System.String" />
            </columns>
          </row>
        </rows>
        <key>DefaultKey</key>
        <total>8.82</total>
        <AddOnKey>0</AddOnKey>
        <data />
        <parameters />
      </table>
    </Data>
  </Data>
 </Root>')

SELECT top 2000
    ID = id.value('(table/id)[1]', 'nvarchar(255)'),
    ec_date = ColumnData.value('Column[1]', 'nvarchar(255)')
    ec_description = ColumnData.value('Column[2]', 'nvarchar(255)')


FROM
    @XMLData.nodes('/Root/Data/Data') AS XTbl(id)
CROSS APPLY
    id.nodes('table/rows/row/columns') AS XTbl2(ColumnData)

結果是這樣的：| 身份證 | ec_date | ec_description | | -------- | -------------- | -------------- | | 54bd0b6f-693e-476f-a80e-0094de4cf3b4 | 空 | 空 | | 9adfe4dd-49be-48fb-a838-009e861be8bd | 空 | 空 | | 9adfe4dd-49be-48fb-a838-009e861be8bd | 空 | 空 |

當有多個行鏈接到一個 ID 時，它會正確地生成多行。

我遇到的問題是我似乎無法從 XML 列中獲取值。

uj5u.com熱心網友回復：

請嘗試以下解決方案。

它將從 SQL Server 2017 開始作業。

值得注意的點：

OUTER APPLY 模擬輸入 XML 中兩個級別之間的父/子關系。
XQuery.nodes()方法 XPath 運算式進行了適當的調整。
t1(p) 和 t2(c) 是父/子（一對多）關系結果集的別名。
高級TRIM(...)功能用于洗掉大括號。該函式是在 SQL Server 2017 中引入的。

查詢陳述句

DECLARE @XMLData XML = 
N'<Root>
    <Data>
        <Data>
            <table>
                <id>{54bd0b6f-693e-476f-a80e-0094de4cf3b4}</id>
                <rows>
                    <row>
                        <columns>
                            <column name="ec_date" value="27-July-2021"
                                    type="System.DateTime"/>
                            <column name="ec_description"
                                    value="Reiskosten woon-werk"
                                    type="System.String"/>
                            <column name="ec_Type" value="XP504"
                                    type="System.String"/>
                            <column name="ec_currency" value="EUR"
                                    type="System.String"/>
                        </columns>
                    </row>
                </rows>
                <key>DefaultKey</key>
                <total>145.14</total>
                <AddOnKey>0</AddOnKey>
                <data/>
                <parameters/>
            </table>
        </Data>
    </Data>
    <Data>
        <Data>
            <table>
                <id>{9adfe4dd-49be-48fb-a838-009e861be8bd}</id>
                <rows>
                    <row>
                        <columns>
                            <column name="ec_date" value="24-August-2021"
                                    type="System.DateTime"/>
                            <column name="ec_description"
                                    value="reiskosten Alkmaar-ZaandamXP502"
                                    type="System.String"/>
                            <column name="ec_Type" value="XP502"
                                    type="System.String"/>
                            <column name="ec_currency" value="EUR"
                                    type="System.String"/>
                        </columns>
                    </row>
                    <row>
                        <columns>
                            <column name="ec_date" value="26-August-2021"
                                    type="System.DateTime"/>
                            <column name="ec_description"
                                    value="reiskosten Alkmaar-Zaandam"
                                    type="System.String"/>
                            <column name="ec_Type" value="XP502"
                                    type="System.String"/>
                            <column name="ec_currency" value="EUR"
                                    type="System.String"/>
                        </columns>
                    </row>
                </rows>
                <key>DefaultKey</key>
                <total>8.82</total>
                <AddOnKey>0</AddOnKey>
                <data/>
                <parameters/>
            </table>
        </Data>
    </Data>
</Root>';

SELECT TRIM('{}' FROM p.value('(id/text())[1]', 'NVARCHAR(255)')) AS ID
    , c.value('(column[@name="ec_date"]/@value)[1]', 'NVARCHAR(255)') AS ec_date
    , c.value('(column[@name="ec_description"]/@value)[1]', 'NVARCHAR(255)') AS ec_description
    , c.value('(column[@name="ec_Type"]/@value)[1]', 'NVARCHAR(255)') AS ec_Type
    , c.value('(column[@name="ec_currency"]/@value)[1]', 'CHAR(3)') AS ec_currency
FROM @XMLData.nodes('/Root/Data/Data/table') AS t1(p)
    OUTER APPLY p.nodes('rows/row/columns') AS t2(c);

輸出

 -------------------------------------- ---------------- --------------------------------- --------- ------------- 
|                  ID                  |    ec_date     |         ec_description          | ec_Type | ec_currency |
 -------------------------------------- ---------------- --------------------------------- --------- ------------- 
| 54bd0b6f-693e-476f-a80e-0094de4cf3b4 | 27-July-2021   | Reiskosten woon-werk            | XP504   | EUR         |
| 9adfe4dd-49be-48fb-a838-009e861be8bd | 24-August-2021 | reiskosten Alkmaar-ZaandamXP502 | XP502   | EUR         |
| 9adfe4dd-49be-48fb-a838-009e861be8bd | 26-August-2021 | reiskosten Alkmaar-Zaandam      | XP502   | EUR         |
 -------------------------------------- ---------------- --------------------------------- --------- -------------

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/365391.html

標籤：sql sql-server xml 查询

上一篇：如何在xslt中包含一個子元素的所有子元素？

下一篇：添加xsi:nil="true"和默認命名空間，并在轉換后生成xmlns=""