locked
shred xml to relational tables - creating foreign keys RRS feed

  • 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.se
    Wednesday, 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
  • SELECT MAX(ID) FROM target_table


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 8, 2015 9:21 PM
  • 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