时间戳

首页 > 解决方案 > 在 SQL XML 数据类型中按顺序循环不同类型的节点

sql-server - 在 SQL XML 数据类型中按顺序循环不同类型的节点

问题描述

在我的 SQL Server 表中，一列的数据类型为 XML。它包含以下数据：

<P1>
    <P2>
        <P3 name='[1] name1', value='val1'> </P3>
        <P4 name='[1] name2', value='val2'> </P4>
        <P3 name='[2] name3', value='val3'> </P3>
        <P5 name='[1] name4', value='val4'> </P5>
        <P3 name='[3] name5', value='val5'> </P3>
    </P2>
</p1>

如何循环这些数据以按顺序获取所有名称和值？

我已经使用存储过程尝试了这个：

BEGIN

        DECLARE @sql1 NVARCHAR(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
        @sql2 NVARCHAR(MAX),
        @sql3 NVARCHAR(MAX);
    set nocount on;
    set @sql1 = N'SELECT tbl.Id' + @CRLF +
    N', c.value(''@name'',''VARCHAR(max)'') AS [name]' + @CRLF +
    N', c.value(''@value'',''VARCHAR(max)'') AS [value]' + @CRLF +
    N'FROM table AS tbl' + @CRLF +
    N'CROSS APPLY tbl.DataXML.nodes(''/P1/P2/P3'') AS t(c)' + @CRLF +
    N'where id=@ID';
    EXEC sp_executesql @sql1, N'@ID [nvarchar](50)', @ID;
    set @sql2 = N'SELECT tbl.Id' + @CRLF +
    N', c.value(''@name'',''VARCHAR(max)'') AS [name]' + @CRLF +
    N', c.value(''@value'',''VARCHAR(max)'') AS [value]' + @CRLF +
    N'FROM table AS tbl' + @CRLF +
    N'CROSS APPLY tbl.DataXML.nodes(''/P1/P2/P4'') AS t(c)' + @CRLF +
    N'where id=@ID';
    EXEC sp_executesql @sql2, N'@ID [nvarchar](50)', @ID;
    set @sql3 = N'SELECT tbl.Id' + @CRLF +
    N', c.value(''@name'',''VARCHAR(max)'') AS [name]' + @CRLF +
    N', c.value(''@value'',''VARCHAR(max)'') AS [value]' + @CRLF +
    N'FROM table AS tbl' + @CRLF +
    N'CROSS APPLY tbl.DataXML.nodes(''/P1/P2/P5'') AS t(c)' + @CRLF +
    N'where id=@ID';

END

在输出中，我按顺序获取名称和值：所有 P3 类型名称，然后是所有 P4，然后是所有 P5。但是我想循环这样我会得到输出，因为它存储在 XML 数据中。

我怎样才能做到这一点？

标签： sql-serverxml

解决方案

declare @x xml = N'
<P1>
    <P2>
        <P3 name="[1] name1" value="val1"> </P3>
        <P4 name="[1] name2" value="val2"> </P4>
        <P3 name="[2] name3" value="val3"> </P3>
        <P5 name="[1] name4" value="val4"> </P5>
        <P3 name="[3] name5" value="val5"> </P3>
    </P2>
</P1>
';

select 
    t.col.value('(@name)[1]', 'varchar(100)') as name,
    t.col.value('(@value)[1]', 'varchar(100)') as value
from @x.nodes('/P1/P2/*[@name and @value]') as t(col);

--or (for P3, P4, P5)
select 
    t.col.value('(@name)[1]', 'varchar(100)') as name,
    t.col.value('(@value)[1]', 'varchar(100)') as value
from @x.nodes('/P1/P2/*[local-name() = ("P3", "P4", "P5")]') as t(col);

推荐阅读