使用SQL将XML子元素提取为列表

我一眼就瞅出问题所在了——你最后加的FOR XML PATH('MeterImportDataItem'), ELEMENTS把原本应该返回的多行记录又重新拼接成了单个XML结果，这就是为啥你只拿到单行输出的原因！

要获取每行对应一个<MeterImportDataItem>的列表，只需要删掉这条FOR XML语句就行。修改后的SQL代码如下：

DECLARE @XML XML = '<MeterImportDataItems> <MeterImportDataItem> <MeterId>-1</MeterId> <GlobalId>cd5116fa-15f9-48c7-946b-1b199550d014</GlobalId> <DeviceId>5029</DeviceId> <MeterName>GREER CENTRAL POINT</MeterName> <MeterNumber>0989745-10</MeterNumber> <MeterTypeName>Orifice</MeterTypeName> <RunStatusCategory>Active</RunStatusCategory> <SystemCategoryId>2</SystemCategoryId> <SystemCategoryName>Intrastate</SystemCategoryName> <DataSourceName>Meter Dashboard</DataSourceName> </MeterImportDataItem> <MeterImportDataItem> <MeterId>-1</MeterId> <GlobalId>26f07f46-c506-4c06-bba9-9ae0e09a72fd</GlobalId> <DeviceId>0</DeviceId> <MeterName>TL MCCRARY 14-11H CRP</MeterName> <MeterNumber>4523401</MeterNumber> <MeterTypeName>Orifice</MeterTypeName> <RunStatusCategory>Temporarily Disconnected</RunStatusCategory> <SystemCategoryId>2</SystemCategoryId> <SystemCategoryName>Intrastate</SystemCategoryName> <DataSourceName>Meter Dashboard</DataSourceName> </MeterImportDataItem> </MeterImportDataItems>'
SELECT 
    C.value('MeterId[1]','INT') AS MeterId
    , C.value('GlobalId[1]','UNIQUEIDENTIFIER') AS GlobalId
    , C.value('DeviceId[1]','INT') AS DeviceId
    , C.value('MeterName[1]','VARCHAR(50)') AS MeterName
    , C.value('MeterNumber[1]','VARCHAR(20)') AS MeterNumber
    , C.value('MeterTypeName[1]','VARCHAR(50)') AS MeterTypeName
    , C.value('RunStatusCategory[1]','VARCHAR(50)') AS RunStatusCategory
    , C.value('SystemCategoryId[1]','INT') AS SystemCategoryId
    , C.value('SystemCategoryName[1]','VARCHAR(50)') AS SystemCategoryName
    , C.value('DataSourceName[1]','VARCHAR(50)') AS DataSourceName
FROM @XML.nodes('MeterImportDataItems/MeterImportDataItem') AS T(C)

为啥原来的代码不行？ #

FOR XML PATH子句的作用是把查询结果转换回XML格式，你本来已经用.nodes()方法把XML拆分成了行集，结果又被这个子句合并成了单个XML文档，自然只能看到单行结果。

如果你的需求确实是提取关系型的多行记录，删掉这个子句就能得到预期的每行对应一个子元素的列表了。

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