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

Filed under:
|

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

Related posts about sql-server-2005

Related posts about tsql