I have an Xml file that i am trying to parse into database tables.
I have some code which does that.. however it doesn't organise the code into the tables appropriately as it returns all products and rooms without any order.
As you can see from below, the xml first defines the room name and RoomId.
What follows then is a list of products that are going to be in that room. These products are all stored within a tables and have been assigned parent and child ids.
The XML then repeats this structure for a different room.
I have taken some of the code out as there is a lot of styling and unnecessary code.
What I would like is a query that can store this data appropriately so that I can store it in the database and then retrieve it in SQL Server Report builder.
cheers
XML
<custom1:RoomGroupRangeStart RoomID="26" RoomName="Bathroom" /> <custom1:SemanticRangeStart.Product> <t:Products HasChild="True" ID="48" Level="1" Name="Plumbing" ParentID="1"/> </custom1:SemanticRangeStart.Product> <custom1:SemanticRangeStart.Product> <t:Products HasChild="True" ID="49" Level="2" Name="Central Heating" ParentID="48" /> </custom1:SemanticRangeStart.Product> <custom1:SemanticRangeStart.Product> <t:Products HasChild="True" ID="50" Level="3" Name="Gas" ParentID="49" /> </custom1:SemanticRangeStart.Product>
First attempt
select nodes.node.value( '@ID', 'int' ) Id, nodes.node.value( '@Name', 'varchar(max)' ) Name, nodes.node.value( '@ParentID', 'int' ) ParentId from @xml.nodes( '//*:Products' ) nodes ( node ) select nodes.node.value ( '@RoomID', 'int' ) RoomID, nodes.node.value ( '@RoomName', 'varchar(max)' ) RoomName from @xml.nodes( '//*:RoomGroupRangeStart' ) nodes ( node )
No comments:
Post a Comment