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

Answer 1

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

Answer 2

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

Answer 3

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