Not able to get desired output while generating xml files from SQL query from SQL Server

Question

I am executing this query

select category "ROOT/category",
question "Category/question",
option1 "Category/option1"
from testDB2 for XML PATH ('ROOT') , ELEMENTS

Presently the database has three entries and the xml file i get is this

<ROOT>
  <ROOT>
    <category>maths</category>
  </ROOT>
  <Category>
    <question>2+2?</question>
    <option1>1</option1>
  </Category>
</ROOT>
<ROOT>
  <ROOT>
    <category>maths</category>
  </ROOT>
  <Category>
    <question>100*0</question>
    <option1>0</option1>
  </Category>
</ROOT>
<ROOT>
  <ROOT>
    <category>chemistry</category>
  </ROOT>
  <Category>
    <question>H2O?</question>
    <option1>water </option1>
  </Category>
</ROOT>

I do not want this, i want a file with just one main Parent node and rest of them as its child and each child can be parent for other child nodes, but there should be just one single main Parent node, in this case each row is a separate parent and there is no main or single parent

I hope I am able to tell my question properly. Thanks

hi there seems to be a problem, my complete description of the question was not posted. — Ray
– Ray, Commented Oct 28, 2009 at 19:10
I am sorry I am not able to figure out how to post the output I am getting from this query on this page, If you can figure out the issue I am encountering that would be great, else i ask people to delete this post so I can start to tell it again in different manner — Ray
– Ray, Commented Oct 28, 2009 at 19:13
You can use the Code Sample button on XML to make it appear in your questions and answers in the future. — Jerry Bullard
– Jerry Bullard, Commented Oct 28, 2009 at 19:24

Guy Wouters · Accepted Answer · 2009-10-28 19:25:03Z

2

try something like this:

select category, question, option1 from testdb2 for xml raw('Category'), elements, root('Categories')

for xml raw: this will make a node for each row in your table, with every column an attribute for that node for xml raw('user'): this is the same as xml raw, but you specify the name of the nodes for xml raw('user') elements: you swith from a attribute view to a node view. every column will be a node in your row node root('Users'): you can use this to name your parent root

hope this helps

answered Oct 28, 2009 at 19:25

Guy Wouters

665 bronze badges

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

Comments

Jerry Bullard · Accepted Answer · 2009-10-28 19:26:07Z

0

I think you want to use the FOR XML AUTO mode to shape your output.

answered Oct 28, 2009 at 19:26

Jerry Bullard

6,10621 silver badges27 bronze badges

Comments

marc_s · Accepted Answer · 2009-10-28 21:49:04Z

0

Not 100% sure what it is you really want, but how about this:

SELECT 
   category '@Name',
   question "Category/question",
   option1 "Category/option1"
FROM
   dbo.testDB2 
FOR XML PATH('Category'), ROOT('ROOT')

Does that get closer to what you want? If I'm not mistaken (can't test right now), this should give you something like:

  <ROOT>
    <Category Name="maths">
      <question>100*0</question>
      <option1>0</option1>
    </Category>
    <Category Name="chemistry">
      <question>H2O?</question>
      <option1>water </option1>
    </Category>
  </ROOT>

If not - could you post a few sample rows of data, and what you expect to get from your SELECT in the end??

Marc

edited Oct 28, 2009 at 21:49

answered Oct 28, 2009 at 20:14

marc_s

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

Collectives™ on Stack Overflow

Not able to get desired output while generating xml files from SQL query from SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related