SQL SERVER FOR XML SYNTAX
- by Raj73
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>
