Answered by:
shred xml to relational tables - creating foreign keys

Question
-
Hi,
I'm shredding the below xml into relational tables. Each element of the xml has it's own table and there is a foreign key to join the tables, you can see this in the below picture.
The process I follow is each relational table I always bring the nesecary xml and store it in the table and when shredding I always look at the parent table.
So for example when processing the seat table, I use seat xml from the parent route table, also taking the ROUTEID from the route table. The reason I do this is all about taking the id from the previous step to create the relationships between the tables. Is this clear? (sorry a bit difficult to explain).
Is there a better way to do this? without taking the xml down to the tables?
The problem with this approach is I have xml stored in most tables and the tables are becoming very large.
<Route Type="OneWay" > <Seat Type="FirstClass"> <Prices> <Price Price="10" /> <Price Price="11" /> </Prices> </Seat> <Seat Type="Economy"> <Prices> <Price Price="5" /> <Price Price="7" /> <Price Price="6" /> </Prices> </Seat> </Route>
Wednesday, October 7, 2015 11:17 AM
Answers
-
Hi KiwiNspain,
Please check if below query help.
DECLARE @XML XML ='<Route Type="OneWay" > <Seat Type="FirstClass"> <Prices> <Price Price="10" /> <Price Price="11" /> </Prices> </Seat> <Seat Type="Economy"> <Prices> <Price Price="5" /> <Price Price="7" /> <Price Price="6" /> </Prices> </Seat> </Route>' SELECT @XML ;WITH Cte AS( SELECT T.n.value('@Type','VARCHAR(99)') AS RouteType,c.*,c2.* FROM @XML.nodes('/Route') T(n) CROSS APPLY ( SELECT T.n.value('@Type','VARCHAR(99)') SeatType FROM @XML.nodes('/Route/Seat') T(n) ) c CROSS APPLY ( SELECT T.n.value('@Price','VARCHAR(99)') Price FROM @XML.nodes('/Route/Seat[@Type=sql:column("c.SeatType")]/Prices/Price') T(n) ) c2) SELECT DENSE_RANK() OVER(PARTITION BY RouteType ORDER BY(SELECT 1)) RouteID,RouteType, DENSE_RANK() OVER(PARTITION BY RouteType,SeatType ORDER BY(SELECT 1)) SeatID, SeatType, ROW_NUMBER() OVER(PARTITION BY RouteType,SeatType ORDER BY(SELECT 1)) PriceID,Price FROM Cte
If you have any question, feel free to let me know.Eric Zhang
TechNet Community Support- Proposed as answer by Eric__Zhang Wednesday, October 14, 2015 9:07 AM
- Marked as answer by Eric__Zhang Thursday, October 15, 2015 8:35 AM
Friday, October 9, 2015 7:56 AM
All replies
-
Are these ID columns IDENTITY columns by chance?
If you remodel the tables, to not have the IDENTITY columns, but generate the IDs with row_number, the problem becomes easier. You could simply shred into temp tables which has the extra XML columns, but the target tables have noe.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seWednesday, October 7, 2015 9:12 PM -
hi, yes the tables have identity columns but thats no problem to change.
I like the idea however how do I keep track of the maximum row numbers. I assume the tmp tables would be truncated after the data has been moved to the target tables, then next execution I would need to know what id to start from?
Thursday, October 8, 2015 7:16 AM -
-
Hi KiwiNspain,
Please check if below query help.
DECLARE @XML XML ='<Route Type="OneWay" > <Seat Type="FirstClass"> <Prices> <Price Price="10" /> <Price Price="11" /> </Prices> </Seat> <Seat Type="Economy"> <Prices> <Price Price="5" /> <Price Price="7" /> <Price Price="6" /> </Prices> </Seat> </Route>' SELECT @XML ;WITH Cte AS( SELECT T.n.value('@Type','VARCHAR(99)') AS RouteType,c.*,c2.* FROM @XML.nodes('/Route') T(n) CROSS APPLY ( SELECT T.n.value('@Type','VARCHAR(99)') SeatType FROM @XML.nodes('/Route/Seat') T(n) ) c CROSS APPLY ( SELECT T.n.value('@Price','VARCHAR(99)') Price FROM @XML.nodes('/Route/Seat[@Type=sql:column("c.SeatType")]/Prices/Price') T(n) ) c2) SELECT DENSE_RANK() OVER(PARTITION BY RouteType ORDER BY(SELECT 1)) RouteID,RouteType, DENSE_RANK() OVER(PARTITION BY RouteType,SeatType ORDER BY(SELECT 1)) SeatID, SeatType, ROW_NUMBER() OVER(PARTITION BY RouteType,SeatType ORDER BY(SELECT 1)) PriceID,Price FROM Cte
If you have any question, feel free to let me know.Eric Zhang
TechNet Community Support- Proposed as answer by Eric__Zhang Wednesday, October 14, 2015 9:07 AM
- Marked as answer by Eric__Zhang Thursday, October 15, 2015 8:35 AM
Friday, October 9, 2015 7:56 AM