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

Question

我需要将csv数据拆分到xml节点列中，并将其显示为单独的记录。我正在使用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，因此下面是语句。

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>');

这是我以前职位的延续。How to split comma separated values stored in XML node Without using function- SQL Server 2012

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

Answer 1

也许这个比以前的要干净一些。

示例

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

编辑-

如果希望看到空值，请使用外部应用程序。