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>

Answer 1

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, [e-mail]

Answer 2

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?

Answer 3

SELECT MAX(ID) FROM target_table

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

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