时间戳

首页 > 解决方案 > 如何在不使用函数的情况下拆分存储在 XML 节点中的逗号分隔值并显示为单独的记录 - SQL Server 2012

sql-server - 如何在不使用函数的情况下拆分存储在 XML 节点中的逗号分隔值并显示为单独的记录 - SQL Server 2012

问题描述

我需要在 xml 节点列中拆分 csv 数据并显示为单独的记录。我正在使用 SQL Server 2012。我想要一个不创建函数的查询。

例如数据如下

ID : 1  
XMLvalue : <BETA>
  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=1,ASSESSMENT=2,ASSESSMENT=3</SERIAL>

<SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL>
</BETA>

ID : 2
XMLvalue : <BETA>
  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=5,ASSESSMENT=6</SERIAL>

<SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL>
</BETA>

ID : 3
XMLvalue : <BETA>
  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL>

<SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL>
</BETA>

输出应该如下

ID     Assessment/PROJECT

1      ASSESSMENT=1    

1      ASSESSMENT=2    

1      ASSESSMENT=3    

2      ASSESSMENT=4    

2      ASSESSMENT=5    

2      ASSESSMENT=6    

3      ASSESSMENT=7    

3      ASSESSMENT=8    

3      ASSESSMENT=9    

1     PROJECT=1

1    PROJECT=2

1   PROJECT=3

2   PROJECT=4

2   PROJECT=5

2   PROJECT=6

3   PROJECT=7

3   PROJECT=8

3   PROJECT=9

我想在不创建函数而只使用查询的情况下实现上述输出。我无法创建 SQL Fiddle，因此以下是下面的创建和插入语句。

CREATE TABLE Chart (StoreID int PRIMARY KEY,
                    XMLvalue xml);

INSERT INTO Chart
VALUES (1, '<BETA>
      <SERIAL NAME="ASSESSMENT"> ASSESSMENT=1,ASSESSMENT=2,ASSESSMENT=3</SERIAL>

    <SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL>
    </BETA>');

INSERT INTO Chart
VALUES (2, '<BETA>
      <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=5,ASSESSMENT=6</SERIAL>

    <SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL>
    </BETA>');

INSERT INTO Chart
VALUES (3, '<BETA>
      <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL>

    <SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL>
    </BETA>');

这是我上一篇文章的延续。 如何在不使用函数的情况下拆分存储在 XML 节点中的逗号分隔值 - SQL Server 2012

请注意，这两个要求的输出是不同的。

标签： sql-serversplitsql-server-2012

解决方案

也许这比以前的帖子干净一点

例子

Select A.ID
      ,C.*
 From  Chart A
 Cross Apply (
                Select Item   = x.v.value('@NAME','VARCHAR(max)')
                      ,Value  = x.v.value('.[1]','VARCHAR(max)')
                 From  XMLvalue.nodes('/BETA/*') x(v)
             ) B
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(B.Value,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) C

退货

ID  RetSeq  RetVal
1   1       ASSESSMENT=1
1   2       ASSESSMENT=2
1   3       ASSESSMENT=3
2   1       ASSESSMENT=4
2   2       ASSESSMENT=5
2   3       ASSESSMENT=6
3   1       ASSESSMENT=7
3   2       ASSESSMENT=8
3   3       ASSESSMENT=9
1   1       PROJECT=1
1   2       PROJECT=2
1   3       PROJECT=3
2   1       PROJECT=4
2   2       PROJECT=5
2   3       PROJECT=6
3   1       PROJECT=7
3   2       PROJECT=8
3   3       PROJECT=9

编辑 -

如果您想查看 NULL 值，请使用 OUTER APPLY。

推荐阅读