Not able to get desired output while generating xml files from SQL query from SQL Server
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
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
I think you want to use the FOR XML AUTO mode to shape your output.
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
精彩评论