SQL SERVER FOR XML SYNTAX

Question

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>

Answer 1

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 a 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 about as close to your requirement as you can get with FOR XML PATH(), ROOT() etc.

Answer 2

Does anyone have a tsql solution for the above problem

Answer 3

Nodes correspond to field names. So if you want your primary nodes to all have different names (i.e., "<SALE1>", "<SALE2>", etc.), then they can't come from the same column.

The output format you asked for will be difficult to query values out of later. (e.g., it will prevent you from telling XPATH or what have you to "look in" a specific "field" for values)

What you should be looking for is this:

<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 & 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>

Anyway, look into books online for SQL-Server. Here are some ideas: FOR XML auto, elements, root("root") FOR XML raw etc...

Answer 4

Can't understand why each node needs a different tag -- that won't be any fun to query -- but anyway, you can do it explicitly (without FOR XML).

Do a WHILE loop over your recordset by incrementing your MIN(PID), and simply SELECT '<' + PNAME + '>' and so on. It will be tricky to format it so it's human-readable like default XML files, but it will still be readable by whatever web page, etc. you have trying to query it.