如何从XML中提取重复节点属性值并生成ID关联的数据表

错误原因 #

1. XPath节点名大小写不匹配：XML中节点为小写column，原查询使用大写Column无法匹配到节点
2. 取值逻辑错误：你需要的字段值存储在column节点的value属性中，原查询直接取节点文本内容，自然返回NULL
3. 按索引取字段稳定性差：建议通过name属性筛选对应字段，避免字段顺序变化导致取值错误

修正后的查询语句 #

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

SELECT TOP 2000
    ID = REPLACE(REPLACE(id.value('(table/id)[1]', 'nvarchar(255)'),'{',''),'}',''),
    ec_date = ColumnData.value('(column[@name="ec_date"]/@value)[1]', 'nvarchar(255)'),
    ec_description = ColumnData.value('(column[@name="ec_description"]/@value)[1]', 'nvarchar(255)'),
    ec_Type = ColumnData.value('(column[@name="ec_Type"]/@value)[1]', 'nvarchar(255)'),
    ec_currency = ColumnData.value('(column[@name="ec_currency"]/@value)[1]', 'nvarchar(255)')
FROM
    @XMLData.nodes('/Root/Data/Data') AS XTbl(id)
CROSS APPLY
    id.nodes('table/rows/row/columns') AS XTbl2(ColumnData)

运行结果 #

内容的提问来源于stack exchange，提问作者Jelmer