SQL SERVER FOR XML SYNTAX
Posted
by Raj73
on Stack Overflow
See other posts from Stack Overflow
or by Raj73
Published on 2010-04-29T16:27:03Z
Indexed on
2010/04/30
6:07 UTC
Read the original article
Hit count: 246
sql-server-2005
|tsql
How can I get an output as follows using FOR XML / sql query. I am not sure how I can get the Column Values as Elements instead of the tables' column Names. I am using sql server 2005
I HAVE TABLE SCEMA AS FOLLOWS
CREATE TABLE PARENT
(
PID INT,
PNAME VARCHAR(20)
)
CREATE TABLE CHILD
(
PID INT,
CID INT,
CNAME VARCHAR(20)
)
CREATE TABLE CHILDVALUE
(
CID INT,
CVALUE VARCHAR(20)
)
INSERT INTO PARENT VALUES (1, 'SALES1')
INSERT INTO PARENT VALUES (2, 'SALES2')
INSERT INTO CHILD VALUES (1, 1, 'FOR01')
INSERT INTO CHILD VALUES (1, 2, 'FOR02')
INSERT INTO CHILD VALUES (2, 3, 'FOR03')
INSERT INTO CHILD VALUES (2, 4, 'FOR04')
INSERT INTO CHILDVALUE VALUES (1, '250000')
INSERT INTO CHILDVALUE VALUES (2, '400000')
INSERT INTO CHILDVALUE VALUES (3, '500000')
INSERT INTO CHILDVALUE VALUES (4, '800000')
The Output I am looking for is as follows
<SALE1>
<FOR01>250000</FOR01>
<FOR02>400000</FOR02>
</SALE1>
<SALE2>
<FOR03>500000</FOR03>
<FOR04>800000</FOR04>
</SALE2>
© Stack Overflow or respective owner