How to split comma separated values stored in XML node and display as individual records Without using function- SQL Server 2012

• Patchai001

  SSC Veteran

  Points: 290

  More actions

  December 12, 2018 at 9:44 am

  #369365

  I have a requirement to split a csv data inside an xml node column and display as individual records. I am using SQL server 2012. I want a query without creating a function.
  The data is as follows for example
  ID : 11                                                   
XMLvalue : <BETA>XMLvalue : <BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=11,ASSESSMENT=,ASSESSMENT=22,ASSESSMENT=,ASSESSMENT=33</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=11,PROJECT=,PROJECT=22,PROJECT=,PROJECT=33</SERIAL></SERIAL>
</BETA></BETA>

  ID : ID : 22
XMLvalue : <BETA>XMLvalue : <BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=44,ASSESSMENT=,ASSESSMENT=55,ASSESSMENT=,ASSESSMENT=66</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=44,PROJECT=,PROJECT=55,PROJECT=,PROJECT=66</SERIAL></SERIAL>
</BETA></BETA>

  ID : ID : 33
XMLvalue : <BETA>XMLvalue : <BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=77,ASSESSMENT=,ASSESSMENT=88,ASSESSMENT=,ASSESSMENT=99</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=77,PROJECT=,PROJECT=88,PROJECT=,PROJECT=99</SERIAL></SERIAL>
</BETA></BETA>
  The output should be as follows
  
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


  I want to achieve the above output without creating a function and only using a query.
  I was not able to create a SQL Fiddle, so following are the create and insert statement below.
  

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

  
  Any Help to this would be greatly appreciated.
  Thanks in advance.
  Patchai

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 12, 2018 at 9:52 am

  #2016119

  Patchai001 - Wednesday, December 12, 2018 9:44 AM

  I have a requirement to split a csv data inside an xml node column and display as individual records. I am using SQL server 2012. I want a query without creating a function.
  The data is as follows for example
  ID : 11                                                   
XMLvalue : <BETA>XMLvalue : <BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=11,ASSESSMENT=,ASSESSMENT=22,ASSESSMENT=,ASSESSMENT=33</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=11,PROJECT=,PROJECT=22,PROJECT=,PROJECT=33</SERIAL></SERIAL>
</BETA></BETA>

  ID : ID : 22
XMLvalue : <BETA>XMLvalue : <BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=44,ASSESSMENT=,ASSESSMENT=55,ASSESSMENT=,ASSESSMENT=66</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=44,PROJECT=,PROJECT=55,PROJECT=,PROJECT=66</SERIAL></SERIAL>
</BETA></BETA>

  ID : ID : 33
XMLvalue : <BETA>XMLvalue : <BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=77,ASSESSMENT=,ASSESSMENT=88,ASSESSMENT=,ASSESSMENT=99</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=77,PROJECT=,PROJECT=88,PROJECT=,PROJECT=99</SERIAL></SERIAL>
</BETA></BETA>
  The output should be as follows
  
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


  I want to achieve the above output without creating a function and only using a query.
  I was not able to create a SQL Fiddle, so following are the create and insert statement below.
  CREATE TABLETABLE Chart Chart
((
StoreID INT     StoreID INT PRIMARYPRIMARY KEYKEY,,
XMLvalue    XMLvalue    XMLXML
);    );

      INSERTINSERT INTOINTO Chart Chart ValuesValues ( (11,<BETA>,<BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=11,ASSESSMENT=,ASSESSMENT=22,ASSESSMENT=,ASSESSMENT=33</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=11,PROJECT=,PROJECT=22,PROJECT=,PROJECT=33</SERIAL></SERIAL>
</BETA>)</BETA>)

      INSERTINSERT INTOINTO Chart Chart ValuesValues ( (22,<BETA>,<BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=44,ASSESSMENT=,ASSESSMENT=55,ASSESSMENT=,ASSESSMENT=66</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=44,PROJECT=,PROJECT=55,PROJECT=,PROJECT=66</SERIAL></SERIAL>
</BETA>)</BETA>)

      INSERTINSERT INTOINTO Chart Chart ValuesValues ( (33,<BETA>,<BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=77,ASSESSMENT=,ASSESSMENT=88,ASSESSMENT=,ASSESSMENT=99</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=77,PROJECT=,PROJECT=88,PROJECT=,PROJECT=99</SERIAL></SERIAL>
</BETA>)</BETA>)
  Any Help to this would be greatly appreciated.
  Thanks in advance.
  Patchai

  Patchai001 - Wednesday, December 12, 2018 9:44 AM

  I have a requirement to split a csv data inside an xml node column and display as individual records. I am using SQL server 2012. I want a query without creating a function.
  The data is as follows for example
  ID : 11                                                   
XMLvalue : <BETA>XMLvalue : <BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=11,ASSESSMENT=,ASSESSMENT=22,ASSESSMENT=,ASSESSMENT=33</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=11,PROJECT=,PROJECT=22,PROJECT=,PROJECT=33</SERIAL></SERIAL>
</BETA></BETA>

  ID : ID : 22
XMLvalue : <BETA>XMLvalue : <BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=44,ASSESSMENT=,ASSESSMENT=55,ASSESSMENT=,ASSESSMENT=66</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=44,PROJECT=,PROJECT=55,PROJECT=,PROJECT=66</SERIAL></SERIAL>
</BETA></BETA>

  ID : ID : 33
XMLvalue : <BETA>XMLvalue : <BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=77,ASSESSMENT=,ASSESSMENT=88,ASSESSMENT=,ASSESSMENT=99</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=77,PROJECT=,PROJECT=88,PROJECT=,PROJECT=99</SERIAL></SERIAL>
</BETA></BETA>
  The output should be as follows
  
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


  I want to achieve the above output without creating a function and only using a query.
  I was not able to create a SQL Fiddle, so following are the create and insert statement below.
  CREATE TABLETABLE Chart Chart
((
StoreID INT     StoreID INT PRIMARYPRIMARY KEYKEY,,
XMLvalue    XMLvalue    XMLXML
);    );

      INSERTINSERT INTOINTO Chart Chart ValuesValues ( (11,<BETA>,<BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=11,ASSESSMENT=,ASSESSMENT=22,ASSESSMENT=,ASSESSMENT=33</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=11,PROJECT=,PROJECT=22,PROJECT=,PROJECT=33</SERIAL></SERIAL>
</BETA>)</BETA>)

      INSERTINSERT INTOINTO Chart Chart ValuesValues ( (22,<BETA>,<BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=44,ASSESSMENT=,ASSESSMENT=55,ASSESSMENT=,ASSESSMENT=66</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=44,PROJECT=,PROJECT=55,PROJECT=,PROJECT=66</SERIAL></SERIAL>
</BETA>)</BETA>)

      INSERTINSERT INTOINTO Chart Chart ValuesValues ( (33,<BETA>,<BETA>
<SERIAL NAME=    <SERIAL NAME="ASSESSMENT""ASSESSMENT"> ASSESSMENT=> ASSESSMENT=77,ASSESSMENT=,ASSESSMENT=88,ASSESSMENT=,ASSESSMENT=99</SERIAL></SERIAL>

  <SERIAL NAME=<SERIAL NAME="PROJECT""PROJECT"> PROJECT=> PROJECT=77,PROJECT=,PROJECT=88,PROJECT=,PROJECT=99</SERIAL></SERIAL>
</BETA>)</BETA>)
  Any Help to this would be greatly appreciated.
  Thanks in advance.
  Patchai

  Looking at the code posted, looks like it needs some cleaning, not just because of the emojis.

• Patchai001

  SSC Veteran

  Points: 290

  More actions

  December 12, 2018 at 10:12 am

  #2016123

  Lynn Pettis - Wednesday, December 12, 2018 9:52 AM

  Looking at the code posted, looks like it needs some cleaning, not just because of the emojis.

  Thanks for the reply Lynn, i have cleaned it up.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 12, 2018 at 10:22 am

  #2016128

  Patchai001 - Wednesday, December 12, 2018 10:12 AM

  Lynn Pettis - Wednesday, December 12, 2018 9:52 AM

  Looking at the code posted, looks like it needs some cleaning, not just because of the emojis.

  Thanks for the reply Lynn, i have cleaned it up.

  Edit:  Never mind, browse hung and didn't scroll back far enough.

  Now, if they could fix the code blocks to stop parsing code into emojis.

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  December 12, 2018 at 1:43 pm

  #2016153

  Lynn Pettis - Wednesday, December 12, 2018 10:22 AM

  Patchai001 - Wednesday, December 12, 2018 10:12 AM

  Lynn Pettis - Wednesday, December 12, 2018 9:52 AM

  Looking at the code posted, looks like it needs some cleaning, not just because of the emojis.

  Thanks for the reply Lynn, i have cleaned it up.

  Edit:  Never mind, browse hung and didn't scroll back far enough.

  Now, if they could fix the code blocks to stop parsing code into emojis.

  The emoji thing kills me!

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001