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