SQLSERVER——XML转数据表输出（E10的PickList转成数据表）

-- 声明 XML 变量并加载 XML 数据
DECLARE @XML AS XML;
SET @XML
    = N'<PickListType>    <Name>UDF_COLLECTION_TJ</Name>    <DisplayName>收款条件</DisplayName>    <Items>      <PickListItem>        <Id>合同签订</Id>        <Sequence>1</Sequence>        <DisplayName>合同签订</DisplayName>      </PickListItem>      <PickListItem>        <Id>出机</Id>        <Sequence>2</Sequence>        <DisplayName>出机</DisplayName>      </PickListItem>      <PickListItem>        <Id>到货</Id>        <Sequence>3</Sequence>        <DisplayName>到货</DisplayName>      </PickListItem>      <PickListItem>        <Id>安装调试</Id>        <Sequence>4</Sequence>        <DisplayName>安装调试</DisplayName>      </PickListItem>      <PickListItem>        <Id>验收</Id>        <Sequence>5</Sequence>        <DisplayName>验收</DisplayName>      </PickListItem>      <PickListItem>        <Id>质保</Id>        <Sequence>6</Sequence>        <DisplayName>质保</DisplayName>      </PickListItem>      <PickListItem>        <Id>票到</Id>        <Sequence>7</Sequence>        <DisplayName>票到</DisplayName>      </PickListItem>    </Items>  </PickListType>';

SELECT t.Name,
       t.DisplayName,
       t.Description,
       t.ItemId,
       t.ItemSequence,
       t.ItemDisplayName
FROM
(
    SELECT T.C.value('(Name/text())[1]', 'NVARCHAR(100)') AS Name,
           T.C.value('(DisplayName/text())[1]', 'NVARCHAR(100)') AS DisplayName,
           T.C.value('(Description/text())[1]', 'NVARCHAR(MAX)') AS Description,
           I.C.value('(Id/text())[1]', 'NVARCHAR(10)') AS ItemId,
           I.C.value('(Sequence/text())[1]', 'INT') AS ItemSequence,
           I.C.value('(DisplayName/text())[1]', 'NVARCHAR(100)') AS ItemDisplayName
    FROM @XML.nodes('/PickListType') AS T(C)
        CROSS APPLY T.C.nodes('Items/PickListItem') AS I(C)
) t
ORDER BY t.ItemSequence

 

--E10的picklist
SELECT * FROM dbo.ConfigurationAccountSet AS cas WHERE cas.FullName LIKE 'PickListType.%'

 

效果：