时间戳

首页 > 解决方案 > 使用 SQL 将子 XML 提取到（子元素的）列表中

sql-server - 使用 SQL 将子 XML 提取到（子元素的）列表中

问题描述

我有一个变量，其中包含集合中的大量元素。我想要一个孩子的列表......每行1个......但这给了我一个单行

在此处输入图像描述

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('MeterImportDataItem'), ELEMENTS

标签： sql-serverxmltsql

解决方案

好吧，我终于想通了。

SELECT 
    T.c.query('.') AS MeterImportDataItem  
FROM   @XML.nodes('/MeterImportDataItems/MeterImportDataItem') T(c)

推荐阅读