SQL SERVER FOR XML SYNTAX
How can I get the output as follows using FOR XML / sql query. I'm not sure how I can get the column values as items instead of the Column Names column names. I am using SQL Server 2005
I have a STONE TABLE FOLLOWING
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 result I am looking for is as follows
<SALE1>
<FOR01>250000</FOR01>
<FOR02>400000</FOR02>
</SALE1>
<SALE2>
<FOR03>500000</FOR03>
<FOR04>800000</FOR04>
</SALE2>
a source to share
Can't figure out why each node needs a different tag - it won't be fun to query - but you can do it explicitly anyway (no FOR XML).
Do a WHILE loop over your recordset, incrementing your MIN (PID) and simple SELECT '<' + PNAME + '>'
and so on. It would be tricky to format it to be readable like XML files by default, but it will still be readable on whatever web page, etc., that you try to request.
a source to share
Using this query here:
SELECT
p.PName '@Name',
(SELECT
c.CName AS '@Name',
(SELECT
cv.CValue AS 'Value'
FROM dbo.CHILDVALUE cv
WHERE cv.CID = c.CID
FOR XML PATH(''), TYPE
)
FROM dbo.CHILD c
WHERE c.PID = p.PID
FOR XML PATH('Child'), TYPE
)
FROM
Parent p
FOR
XML PATH('Node'), ROOT('Root')
you can get the result something like this:
<Root>
<Node Name="SALES1">
<Child Name="FOR01">
<Value>250000</Value>
</Child>
<Child Name="FOR02">
<Value>400000</Value>
</Child>
</Node>
<Node Name="SALES2">
<Child Name="FOR03">
<Value>500000</Value>
</Child>
<Child Name="FOR04">
<Value>800000</Value>
</Child>
</Node>
</Root>
which is probably close to your requirement as you can get with FOR XML PATH(), ROOT()
, etc.
a source to share
The nodes correspond to the field names. Therefore, if you want your primary nodes to have different names (i.e. "<SALE1>", "<SALE2>"
, etc.), They cannot come from the same column.
The output format you asked for will be difficult to query for values later. (for example, this will prevent you from telling XPATH or what you need to "look" in a specific field for values)
What you should be looking for is:
<SALE>
<ID>1</ID>
<FOR>
<ID>1</ID>
<NUM>250000</NUM>
</FOR>
<FOR>
<ID>2</ID>
<NUM>400000</NUM>
</FOR>
</SALE>
<SALE>
<ID>2</ID>
<FOR>
<ID>3</ID>
<NUM>500000</NUM>
</FOR>
<FOR>
<ID>4</ID>
<NUM>800000</NUM>
</FOR>
</SALE>
Or, alternatively, some combination of attributes and elements like this:
<SALE ID="1">
<FOR ID="1">
<NUM>250000</NUM>
</FOR>
<FOR ID="2">
<NUM>400000</NUM>
</FOR>
</SALE>
<SALE ID="2">
<FOR ID="3">
<NUM>500000</NUM>
</FOR>
<FOR ID="4">
<NUM>800000</NUM>
</FOR>
</SALE>
Either way, study the books online for SQL-Server. Here are some ideas: FOR XML auto, elements, root ("root") FOR XML raw, etc ...
a source to share