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>

dave · Accepted Answer · 2010-08-03 17:28:39Z

1

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.

answered Aug 3, 2010 at 17:28

dave

1,36410 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2010-04-29 17:00:14Z

0

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.

answered Apr 29, 2010 at 17:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

3 Comments

Raj73 Over a year ago

Thanks for the reply. I was also able to get something like you have but I really wanted the column names as elements. So is what I am asking for not possible with any of FOR XML syntax ? Also I can manipulate the tables in a stored procedure, internally use some temp tables etc but still not clear as to how I can get the result I need.

marc_s Over a year ago

@Raj73: from all I know about SQL Server and its FOR XML support, what you're trying to do cannot be accomplished. If you're not happy with this result you can get, you'd have to use some programming language like C# or VB.NET or something to read the data and render the XML.

Raj73 Over a year ago

thanks, we will have a million rows and I didn't want to pull it using C# / ado.net/linq to generate and render the xml. I was planning to run the query/ Stored Proc from command line and output the result as an xml file. Maybe using a programming lang is the only way that this can be done.

dave · Accepted Answer · 2010-08-03 17:05:58Z

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

Collectives™ on Stack Overflow

SQL SERVER FOR XML SYNTAX

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related