December 12, 2018 at 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 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
December 12, 2018 at 9:52 am
Patchai001 - Wednesday, December 12, 2018 9:44 AMI 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 exampleID : 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/PROJECT1 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 AMI 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 exampleID : 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/PROJECT1 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.
December 12, 2018 at 10:12 am
Lynn Pettis - Wednesday, December 12, 2018 9:52 AMLooking 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.
December 12, 2018 at 10:22 am
Patchai001 - Wednesday, December 12, 2018 10:12 AMLynn Pettis - Wednesday, December 12, 2018 9:52 AMLooking 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.
December 12, 2018 at 1:43 pm
Lynn Pettis - Wednesday, December 12, 2018 10:22 AMPatchai001 - Wednesday, December 12, 2018 10:12 AMLynn Pettis - Wednesday, December 12, 2018 9:52 AMLooking 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!
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply