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

Transact-SQL
#1

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

#2

try #1

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

;with details
as
(
	SELECT A.StoreID,  
		Split.a.value('.', 'VARCHAR(100)') AS Data  
	FROM  
	(
		SELECT StoreID,  
			CAST ('<M>' + REPLACE(XMLvalue.value('(//*[local-name()="BETA"])[1]', 'nvarchar(max)'), ',', '</M><M>') + '</M>' AS XML) AS Data  
		FROM  #chart
	) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
)
select a.StoreID,
       XMLvalue.value('(//*[local-name()="BETA"])[1]', 'nvarchar(max)') as booya,
	   b.Data
  from #chart a
  join details b on a.StoreID = b.StoreID
  order by a.StoreID, b.Data


drop table #chart