December 7, 2009 at 6:36 am
I want to form an XML document something like below in order to send this data to frontend( .net) to create tree structure.Herewith I have attached the sample Data in excel.
like need to write select ..... from table FOR XML AUTO,TYPE,ELEMENTS
<item id=16 NAME='Application'>
<item id=17 NAME='3rd Party Applications'>
<item id=18 NAME='Compatibility issues'/>
<item id=19 NAME='Unable to install'/>
<item id=19 NAME='Unable to launch'/>
<item id=19 NAME='Unable to uninstall'/>
</item>
<item>
<item id=78 NAME='IE'>
<item id=79 NAME='Browsing'>
<item id=81 NAME='Cookies Related'/>
<item id=82 NAME='DLL Corruption'/>
<item id=83 NAME='DNS issues'/>
...
...
</item>
<item>
<item>
....
...
..
</item>
December 8, 2009 at 1:15 pm
You might want to move this to a more suitable forum.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 8, 2009 at 2:03 pm
moved.
Please pay attention to where you post.
December 8, 2009 at 2:35 pm
The nodes structure you provided as expected result is incomplete. You have 4 unclosed nodes.
This is really hard to notice since you're using the same name ("item") to describe at least two nest levels. It's almost impossible to figure out which element belongs to which parent element, since all are named the same.
You might want to find a different name for the parent element... Fortunately, SQL Server will protect us for getting strange results like that... (at least if the data are collected the "easy way"...)
When using "item2" as the name for the parent node the following would work:
SELECT item2.id AS id , item2.name AS name ,item.id, item.name
FROM @t item2
INNER JOIN @t item ON item.parentid=item2.id
ORDER BY item2.id
FOR XML auto
/*result set (partial):
<item2 id="16" name="Application">
<item id="17" name="3rd Party Applications" />
<item id="51" name="Other MS Applications" />
<item id="23" name="Inbox MS Vista Applications" />
</item2>
<item2 id="17" name="3rd Party Applications">
<item id="18" name="Compatibility issues" />
<item id="19" name="Unable to install" />
<item id="20" name="Unable to launch" />
<item id="21" name="Unable to uninstall" />
<item id="22" name="Unexpected behavior (crash)" />
</item2>
*/
A few thoughts on top:
1) You should post XML related question in the XML forum. It doesn't "harm" the site if you pick the wrong forum but it will reduce the number of people who would actually see your post, therewith reducing your chance to get a fast/accurate reply.
2) When posting sample data, you shouldn't just post an Excel file since some of us will simply skip your post if we do have to spend any time getting the data ready to use.
The changes I made were simple in your case:
a) DDL for a table:
DECLARE @t TABLE (Id INT ,Namevarchar(60), ParentId int)
b) prepare for insert:
INSERT INTO @t
c) add the formula [="select " & A2 & " ,'" & B2 & "'," &C2 & " union all"] in field D2 in your Excel and expand it down the rows.
d) remove the very last "union all" and copy the content of column D into SSMS.
... and the data are ready to use. That easy. For more details on how to post sample data please see the first link in my signature if you have the need for assistance again.
Regards,
Lutz
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply