none
work with xml in Transact SQL RRS feed

  • Question

  • I have a xml-text file. I need to write a stored procedure xml text in input parameters. Procedure what that will parse xml and returns three tables from the input xml-text. The input xml:

    <OrdersWithTheirCopies>
     <Order OrderID="1" OrderNumber="Order #1">
      <OrderLines>
       <OrderLine OrderLineID="1" LineNumber="Line #1" ItemDescription="pipe - 100" />
       <OrderLine OrderLineID="2" LineNumber="Line #2" ItemDescription="plate 10" />
       <OrderLine OrderLineID="3" LineNumber="Line #3" ItemDescription="glass 1 mm." />
      </OrderLines>
      <OrderCopies>
       <Copy CopyNumber="1" OrderHistoryID="3">
        <OrderLineHistory OrderLineID="1" OrderLineHistoryID="8" ItemDescription="pipe - 100" />
        <OrderLineHistory OrderLineID="2" OrderLineHistoryID="9" ItemDescription="plate 10" />
        <OrderLineHistory OrderLineID="3" OrderLineHistoryID="10" ItemDescription="glass 1 mm." />
       </Copy>
       <Copy CopyNumber="2" OrderHistoryID="4">
        <OrderLineHistory OrderLineID="1" OrderLineHistoryID="11" ItemDescription="pipe - 100" />
        <OrderLineHistory OrderLineID="2" OrderLineHistoryID="12" ItemDescription="plate 10" />
        <OrderLineHistory OrderLineID="3" OrderLineHistoryID="13" ItemDescription="glass 1 mm." />
       </Copy>
       <Copy CopyNumber="3" OrderHistoryID="6">
        <OrderLineHistory OrderLineID="1" OrderLineHistoryID="16" ItemDescription="pipe - 100" />
        <OrderLineHistory OrderLineID="2" OrderLineHistoryID="17" ItemDescription="plate 10" />
        <OrderLineHistory OrderLineID="3" OrderLineHistoryID="18" ItemDescription="glass 1 mm." />
       </Copy>
      </OrderCopies>
     </Order>
     <Order OrderID="2" OrderNumber="Order #2">
      <OrderLines>
       <OrderLine OrderLineID="4" LineNumber="Line #1" ItemDescription="pipe - 200" />
       <OrderLine OrderLineID="5" LineNumber="Line #2" ItemDescription="plate 20" />
      </OrderLines>
      <OrderCopies>
       <Copy CopyNumber="1" OrderHistoryID="2">
        <OrderLineHistory OrderLineID="4" OrderLineHistoryID="6" ItemDescription="pipe - 200" />
        <OrderLineHistory OrderLineID="5" OrderLineHistoryID="7" ItemDescription="plate 20" />
       </Copy>
       <Copy CopyNumber="2" OrderHistoryID="5">
        <OrderLineHistory OrderLineID="4" OrderLineHistoryID="14" ItemDescription="pipe - 200" />
        <OrderLineHistory OrderLineID="5" OrderLineHistoryID="15" ItemDescription="plate 20" />
       </Copy>
      </OrderCopies>
     </Order>
     <Order OrderID="3" OrderNumber="Order #3">
      <OrderLines>
       <OrderLine OrderLineID="6" LineNumber="Line #1" ItemDescription="pipe - 300" />
       <OrderLine OrderLineID="7" LineNumber="Line #2" ItemDescription="plate - 30" />
       <OrderLine OrderLineID="8" LineNumber="Line #3" ItemDescription="glass - 3 mm" />
       <OrderLine OrderLineID="9" LineNumber="Line #4" ItemDescription="pipe - 330" />
       <OrderLine OrderLineID="10" LineNumber="Line #5" ItemDescription="plate - 33" />
      </OrderLines>
      <OrderCopies>
       <Copy CopyNumber="1" OrderHistoryID="1">
        <OrderLineHistory OrderLineID="6" OrderLineHistoryID="1" ItemDescription="pipe - 300" />
        <OrderLineHistory OrderLineID="7" OrderLineHistoryID="2" ItemDescription="plate - 30" />
        <OrderLineHistory OrderLineID="8" OrderLineHistoryID="3" ItemDescription="glass - 3 mm" />
        <OrderLineHistory OrderLineID="9" OrderLineHistoryID="4" ItemDescription="pipe - 330" />
        <OrderLineHistory OrderLineID="10" OrderLineHistoryID="5" ItemDescription="plate - 33" />
       </Copy>
      </OrderCopies>
     </Order>
    </OrdersWithTheirCopies>
    The output three tables:
    
    <pre lang="x-sql">OrderID   OrderNumber
    ----------- -------------
    1      Order #1
    2      Order #2
    3      Order #3
    
    (3 row(s) affected)
    
    OrderHistoryID OrderFK   CopyNumber
    -------------- ----------- -----------
    3       1      1
    4       1      2
    6       1      3
    2       2      1
    5       2      2
    1       3      1
    
    (6 row(s) affected)
    
    OrderLineHistoryID OrderFK   OrderLineFK ItemDescription
    ------------------ ----------- ----------- --------------------
    8         1      1      pipe - 100
    9         1      2      plate 10
    10         1      3      glass 1 mm.
    11         1      1      pipe - 100
    12         1      2      plate 10
    13         1      3      glass 1 mm.
    16         1      1      pipe - 100
    17         1      2      plate 10
    18         1      3      glass 1 mm.
    6         2      4      pipe - 200
    7         2      5      plate 20
    14         2      4      pipe - 200
    15         2      5      plate 20
    1         3      6      pipe - 300
    2         3      7      plate - 30
    3         3      8      glass - 3 mm
    4         3      9      pipe - 330
    5         3      10     plate - 33
    
    
    Can you help me with it? Because I have never done it before.  And give me some docs for it.
    Monday, November 1, 2010 3:07 PM

Answers

  • Here is a possible solution. since columns [OrderFK] and [OrderLineFK] seems to be alias, It would be good to know which elements / attributes are you referencing from these alias.

    USE tempdb;
    GO
    DECLARE @x xml;
    
    SET @x = '<OrdersWithTheirCopies>
     <Order OrderID="1" OrderNumber="Order #1">
     <OrderLines>
     <OrderLine OrderLineID="1" LineNumber="Line #1" ItemDescription="pipe - 100" />
     <OrderLine OrderLineID="2" LineNumber="Line #2" ItemDescription="plate 10" />
     <OrderLine OrderLineID="3" LineNumber="Line #3" ItemDescription="glass 1 mm." />
     </OrderLines>
     <OrderCopies>
     <Copy CopyNumber="1" OrderHistoryID="3">
     <OrderLineHistory OrderLineID="1" OrderLineHistoryID="8" ItemDescription="pipe - 100" />
     <OrderLineHistory OrderLineID="2" OrderLineHistoryID="9" ItemDescription="plate 10" />
     <OrderLineHistory OrderLineID="3" OrderLineHistoryID="10" ItemDescription="glass 1 mm." />
     </Copy>
     <Copy CopyNumber="2" OrderHistoryID="4">
     <OrderLineHistory OrderLineID="1" OrderLineHistoryID="11" ItemDescription="pipe - 100" />
     <OrderLineHistory OrderLineID="2" OrderLineHistoryID="12" ItemDescription="plate 10" />
     <OrderLineHistory OrderLineID="3" OrderLineHistoryID="13" ItemDescription="glass 1 mm." />
     </Copy>
     <Copy CopyNumber="3" OrderHistoryID="6">
     <OrderLineHistory OrderLineID="1" OrderLineHistoryID="16" ItemDescription="pipe - 100" />
     <OrderLineHistory OrderLineID="2" OrderLineHistoryID="17" ItemDescription="plate 10" />
     <OrderLineHistory OrderLineID="3" OrderLineHistoryID="18" ItemDescription="glass 1 mm." />
     </Copy>
     </OrderCopies>
     </Order>
     <Order OrderID="2" OrderNumber="Order #2">
     <OrderLines>
     <OrderLine OrderLineID="4" LineNumber="Line #1" ItemDescription="pipe - 200" />
     <OrderLine OrderLineID="5" LineNumber="Line #2" ItemDescription="plate 20" />
     </OrderLines>
     <OrderCopies>
     <Copy CopyNumber="1" OrderHistoryID="2">
     <OrderLineHistory OrderLineID="4" OrderLineHistoryID="6" ItemDescription="pipe - 200" />
     <OrderLineHistory OrderLineID="5" OrderLineHistoryID="7" ItemDescription="plate 20" />
     </Copy>
     <Copy CopyNumber="2" OrderHistoryID="5">
     <OrderLineHistory OrderLineID="4" OrderLineHistoryID="14" ItemDescription="pipe - 200" />
     <OrderLineHistory OrderLineID="5" OrderLineHistoryID="15" ItemDescription="plate 20" />
     </Copy>
     </OrderCopies>
     </Order>
     <Order OrderID="3" OrderNumber="Order #3">
     <OrderLines>
     <OrderLine OrderLineID="6" LineNumber="Line #1" ItemDescription="pipe - 300" />
     <OrderLine OrderLineID="7" LineNumber="Line #2" ItemDescription="plate - 30" />
     <OrderLine OrderLineID="8" LineNumber="Line #3" ItemDescription="glass - 3 mm" />
     <OrderLine OrderLineID="9" LineNumber="Line #4" ItemDescription="pipe - 330" />
     <OrderLine OrderLineID="10" LineNumber="Line #5" ItemDescription="plate - 33" />
     </OrderLines>
     <OrderCopies>
     <Copy CopyNumber="1" OrderHistoryID="1">
     <OrderLineHistory OrderLineID="6" OrderLineHistoryID="1" ItemDescription="pipe - 300" />
     <OrderLineHistory OrderLineID="7" OrderLineHistoryID="2" ItemDescription="plate - 30" />
     <OrderLineHistory OrderLineID="8" OrderLineHistoryID="3" ItemDescription="glass - 3 mm" />
     <OrderLineHistory OrderLineID="9" OrderLineHistoryID="4" ItemDescription="pipe - 330" />
     <OrderLineHistory OrderLineID="10" OrderLineHistoryID="5" ItemDescription="plate - 33" />
     </Copy>
     </OrderCopies>
     </Order>
    </OrdersWithTheirCopies>';
    
    SELECT
    	O.x.value('@OrderID[1]', 'int') AS OrderID,
    	O.x.value('@OrderNumber[1]', 'varchar(50)') AS OrderNumber
    FROM
    	@x.nodes('/OrdersWithTheirCopies/Order') AS O(x);
    
    SELECT
    	C.x.value('@OrderHistoryID[1]', 'int') AS OrderHistoryID,
    	O.x.value('@OrderID[1]', 'int') AS OrderID,
    	C.x.value('@CopyNumber[1]', 'int') AS CopyNumber
    FROM
      @x.nodes('/OrdersWithTheirCopies/Order') AS O(x)
      CROSS APPLY
      O.x.nodes('OrderCopies/Copy') AS C(x);
    
    SELECT
    	L.x.value('@OrderLineHistoryID[1]', 'int') AS OrderLineHistoryID,
    	O.x.value('@OrderID[1]', 'int') AS OrderID,
    	L.x.value('@OrderLineID[1]', 'int') AS OrderLineID,
    	L.x.value('@ItemDescription[1]', 'varchar(max)') AS ItemDescription
    FROM
      @x.nodes('/OrdersWithTheirCopies/Order') AS O(x)
      CROSS APPLY
      O.x.nodes('OrderCopies/Copy') AS C(x)
      CROSS APPLY
      C.x.nodes('OrderLineHistory') AS L(x);
    GO


    AMB

    Some guidelines for posting questions...

    • Marked as answer by SunnyIngulia Monday, November 1, 2010 4:20 PM
    Monday, November 1, 2010 4:17 PM
    Moderator
  • Here is one example of how you can do it. Hopefully you should be able to work it out. Read the documentation on it here.

    DECLARE @inputXML XML = '<OrdersWithTheirCopies>
     <Order OrderID="1" OrderNumber="Order #1">
     <OrderLines>
      <OrderLine OrderLineID="1" LineNumber="Line #1" ItemDescription="pipe - 100" />
      <OrderLine OrderLineID="2" LineNumber="Line #2" ItemDescription="plate 10" />
      <OrderLine OrderLineID="3" LineNumber="Line #3" ItemDescription="glass 1 mm." />
     </OrderLines>
     <OrderCopies>
      <Copy CopyNumber="1" OrderHistoryID="3">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="8" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="9" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="10" ItemDescription="glass 1 mm." />
      </Copy>
      <Copy CopyNumber="2" OrderHistoryID="4">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="11" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="12" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="13" ItemDescription="glass 1 mm." />
      </Copy>
      <Copy CopyNumber="3" OrderHistoryID="6">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="16" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="17" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="18" ItemDescription="glass 1 mm." />
      </Copy>
     </OrderCopies>
     </Order>
    </OrdersWithTheirCopies>'
    
    SELECT tbl.cols.value('./@OrderID', 'int'),
    	tbl.cols.value('./@OrderNumber', 'varchar(max)')
    FROM @inputXML.nodes('//Order') as tbl(cols)
    
    SELECT tbl.cols.value('./@OrderHistoryID', 'int'),
    	tbl.cols.value('../../@OrderID', 'int')
    FROM @inputXML.nodes('//Order/OrderCopies/Copy') as tbl(cols)
    

    every day is a school day
    • Marked as answer by SunnyIngulia Monday, November 1, 2010 4:20 PM
    Monday, November 1, 2010 3:30 PM
    Moderator
  • Try

    declare @xml XML = '<OrdersWithTheirCopies>
     <Order OrderID="1" OrderNumber="Order #1">
     <OrderLines>
      <OrderLine OrderLineID="1" LineNumber="Line #1" ItemDescription="pipe - 100" />
      <OrderLine OrderLineID="2" LineNumber="Line #2" ItemDescription="plate 10" />
      <OrderLine OrderLineID="3" LineNumber="Line #3" ItemDescription="glass 1 mm." />
     </OrderLines>
     <OrderCopies>
      <Copy CopyNumber="1" OrderHistoryID="3">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="8" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="9" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="10" ItemDescription="glass 1 mm." />
      </Copy>
      <Copy CopyNumber="2" OrderHistoryID="4">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="11" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="12" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="13" ItemDescription="glass 1 mm." />
      </Copy>
      <Copy CopyNumber="3" OrderHistoryID="6">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="16" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="17" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="18" ItemDescription="glass 1 mm." />
      </Copy>
     </OrderCopies>
     </Order>
     <Order OrderID="2" OrderNumber="Order #2">
     <OrderLines>
      <OrderLine OrderLineID="4" LineNumber="Line #1" ItemDescription="pipe - 200" />
      <OrderLine OrderLineID="5" LineNumber="Line #2" ItemDescription="plate 20" />
     </OrderLines>
     <OrderCopies>
      <Copy CopyNumber="1" OrderHistoryID="2">
      <OrderLineHistory OrderLineID="4" OrderLineHistoryID="6" ItemDescription="pipe - 200" />
      <OrderLineHistory OrderLineID="5" OrderLineHistoryID="7" ItemDescription="plate 20" />
      </Copy>
      <Copy CopyNumber="2" OrderHistoryID="5">
      <OrderLineHistory OrderLineID="4" OrderLineHistoryID="14" ItemDescription="pipe - 200" />
      <OrderLineHistory OrderLineID="5" OrderLineHistoryID="15" ItemDescription="plate 20" />
      </Copy>
     </OrderCopies>
     </Order>
     <Order OrderID="3" OrderNumber="Order #3">
     <OrderLines>
      <OrderLine OrderLineID="6" LineNumber="Line #1" ItemDescription="pipe - 300" />
      <OrderLine OrderLineID="7" LineNumber="Line #2" ItemDescription="plate - 30" />
      <OrderLine OrderLineID="8" LineNumber="Line #3" ItemDescription="glass - 3 mm" />
      <OrderLine OrderLineID="9" LineNumber="Line #4" ItemDescription="pipe - 330" />
      <OrderLine OrderLineID="10" LineNumber="Line #5" ItemDescription="plate - 33" />
     </OrderLines>
     <OrderCopies>
      <Copy CopyNumber="1" OrderHistoryID="1">
      <OrderLineHistory OrderLineID="6" OrderLineHistoryID="1" ItemDescription="pipe - 300" />
      <OrderLineHistory OrderLineID="7" OrderLineHistoryID="2" ItemDescription="plate - 30" />
      <OrderLineHistory OrderLineID="8" OrderLineHistoryID="3" ItemDescription="glass - 3 mm" />
      <OrderLineHistory OrderLineID="9" OrderLineHistoryID="4" ItemDescription="pipe - 330" />
      <OrderLineHistory OrderLineID="10" OrderLineHistoryID="5" ItemDescription="plate - 33" />
      </Copy>
     </OrderCopies>
     </Order>
    </OrdersWithTheirCopies>'
    
    SELECT tbl.cols.value('./@OrderID', 'int') as OrderID,
    	tbl.cols.value('./@OrderNumber', 'varchar(max)') as OrderNumber
    --into #Orders	
    FROM @XML.nodes('//Order') as tbl(cols)
    
    --select * from #Orders
    
    SELECT tbl.cols.value('./@OrderHistoryID', 'int') as OrderHistoryID,
      tbl.cols.value('./@CopyNumber','int') as CopyNumber,
    	tbl.cols.value('../../@OrderID', 'int') as OrderID
    FROM @XML.nodes('//Order/OrderCopies/Copy') as tbl(cols)
    
    SELECT tbl.cols.value('./@OrderLineID', 'int') as OrderLineID,
      tbl.cols.value('./@OrderLineHistoryID', 'int') as OrderLineHistoryID,
      tbl.cols.value('./@ItemDescription', 'varchar(max)') as ItemDescription,
      tbl.cols.value('./../@CopyNumber','int') as CopyNumber,
      tbl.cols.value('./../../../@OrderID','int') as OrderID
    FROM @XML.nodes('//Order/OrderCopies/Copy/OrderLineHistory') as tbl(cols)
    
    
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by SunnyIngulia Monday, November 1, 2010 4:20 PM
    Monday, November 1, 2010 3:57 PM
    Moderator

All replies

  • Here is one example of how you can do it. Hopefully you should be able to work it out. Read the documentation on it here.

    DECLARE @inputXML XML = '<OrdersWithTheirCopies>
     <Order OrderID="1" OrderNumber="Order #1">
     <OrderLines>
      <OrderLine OrderLineID="1" LineNumber="Line #1" ItemDescription="pipe - 100" />
      <OrderLine OrderLineID="2" LineNumber="Line #2" ItemDescription="plate 10" />
      <OrderLine OrderLineID="3" LineNumber="Line #3" ItemDescription="glass 1 mm." />
     </OrderLines>
     <OrderCopies>
      <Copy CopyNumber="1" OrderHistoryID="3">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="8" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="9" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="10" ItemDescription="glass 1 mm." />
      </Copy>
      <Copy CopyNumber="2" OrderHistoryID="4">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="11" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="12" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="13" ItemDescription="glass 1 mm." />
      </Copy>
      <Copy CopyNumber="3" OrderHistoryID="6">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="16" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="17" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="18" ItemDescription="glass 1 mm." />
      </Copy>
     </OrderCopies>
     </Order>
    </OrdersWithTheirCopies>'
    
    SELECT tbl.cols.value('./@OrderID', 'int'),
    	tbl.cols.value('./@OrderNumber', 'varchar(max)')
    FROM @inputXML.nodes('//Order') as tbl(cols)
    
    SELECT tbl.cols.value('./@OrderHistoryID', 'int'),
    	tbl.cols.value('../../@OrderID', 'int')
    FROM @inputXML.nodes('//Order/OrderCopies/Copy') as tbl(cols)
    

    every day is a school day
    • Marked as answer by SunnyIngulia Monday, November 1, 2010 4:20 PM
    Monday, November 1, 2010 3:30 PM
    Moderator
  • Thanks a lot!!!! I try.
    Monday, November 1, 2010 3:33 PM
  • Try

    declare @xml XML = '<OrdersWithTheirCopies>
     <Order OrderID="1" OrderNumber="Order #1">
     <OrderLines>
      <OrderLine OrderLineID="1" LineNumber="Line #1" ItemDescription="pipe - 100" />
      <OrderLine OrderLineID="2" LineNumber="Line #2" ItemDescription="plate 10" />
      <OrderLine OrderLineID="3" LineNumber="Line #3" ItemDescription="glass 1 mm." />
     </OrderLines>
     <OrderCopies>
      <Copy CopyNumber="1" OrderHistoryID="3">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="8" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="9" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="10" ItemDescription="glass 1 mm." />
      </Copy>
      <Copy CopyNumber="2" OrderHistoryID="4">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="11" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="12" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="13" ItemDescription="glass 1 mm." />
      </Copy>
      <Copy CopyNumber="3" OrderHistoryID="6">
      <OrderLineHistory OrderLineID="1" OrderLineHistoryID="16" ItemDescription="pipe - 100" />
      <OrderLineHistory OrderLineID="2" OrderLineHistoryID="17" ItemDescription="plate 10" />
      <OrderLineHistory OrderLineID="3" OrderLineHistoryID="18" ItemDescription="glass 1 mm." />
      </Copy>
     </OrderCopies>
     </Order>
     <Order OrderID="2" OrderNumber="Order #2">
     <OrderLines>
      <OrderLine OrderLineID="4" LineNumber="Line #1" ItemDescription="pipe - 200" />
      <OrderLine OrderLineID="5" LineNumber="Line #2" ItemDescription="plate 20" />
     </OrderLines>
     <OrderCopies>
      <Copy CopyNumber="1" OrderHistoryID="2">
      <OrderLineHistory OrderLineID="4" OrderLineHistoryID="6" ItemDescription="pipe - 200" />
      <OrderLineHistory OrderLineID="5" OrderLineHistoryID="7" ItemDescription="plate 20" />
      </Copy>
      <Copy CopyNumber="2" OrderHistoryID="5">
      <OrderLineHistory OrderLineID="4" OrderLineHistoryID="14" ItemDescription="pipe - 200" />
      <OrderLineHistory OrderLineID="5" OrderLineHistoryID="15" ItemDescription="plate 20" />
      </Copy>
     </OrderCopies>
     </Order>
     <Order OrderID="3" OrderNumber="Order #3">
     <OrderLines>
      <OrderLine OrderLineID="6" LineNumber="Line #1" ItemDescription="pipe - 300" />
      <OrderLine OrderLineID="7" LineNumber="Line #2" ItemDescription="plate - 30" />
      <OrderLine OrderLineID="8" LineNumber="Line #3" ItemDescription="glass - 3 mm" />
      <OrderLine OrderLineID="9" LineNumber="Line #4" ItemDescription="pipe - 330" />
      <OrderLine OrderLineID="10" LineNumber="Line #5" ItemDescription="plate - 33" />
     </OrderLines>
     <OrderCopies>
      <Copy CopyNumber="1" OrderHistoryID="1">
      <OrderLineHistory OrderLineID="6" OrderLineHistoryID="1" ItemDescription="pipe - 300" />
      <OrderLineHistory OrderLineID="7" OrderLineHistoryID="2" ItemDescription="plate - 30" />
      <OrderLineHistory OrderLineID="8" OrderLineHistoryID="3" ItemDescription="glass - 3 mm" />
      <OrderLineHistory OrderLineID="9" OrderLineHistoryID="4" ItemDescription="pipe - 330" />
      <OrderLineHistory OrderLineID="10" OrderLineHistoryID="5" ItemDescription="plate - 33" />
      </Copy>
     </OrderCopies>
     </Order>
    </OrdersWithTheirCopies>'
    
    SELECT tbl.cols.value('./@OrderID', 'int') as OrderID,
    	tbl.cols.value('./@OrderNumber', 'varchar(max)') as OrderNumber
    --into #Orders	
    FROM @XML.nodes('//Order') as tbl(cols)
    
    --select * from #Orders
    
    SELECT tbl.cols.value('./@OrderHistoryID', 'int') as OrderHistoryID,
      tbl.cols.value('./@CopyNumber','int') as CopyNumber,
    	tbl.cols.value('../../@OrderID', 'int') as OrderID
    FROM @XML.nodes('//Order/OrderCopies/Copy') as tbl(cols)
    
    SELECT tbl.cols.value('./@OrderLineID', 'int') as OrderLineID,
      tbl.cols.value('./@OrderLineHistoryID', 'int') as OrderLineHistoryID,
      tbl.cols.value('./@ItemDescription', 'varchar(max)') as ItemDescription,
      tbl.cols.value('./../@CopyNumber','int') as CopyNumber,
      tbl.cols.value('./../../../@OrderID','int') as OrderID
    FROM @XML.nodes('//Order/OrderCopies/Copy/OrderLineHistory') as tbl(cols)
    
    
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by SunnyIngulia Monday, November 1, 2010 4:20 PM
    Monday, November 1, 2010 3:57 PM
    Moderator
  • Here is a possible solution. since columns [OrderFK] and [OrderLineFK] seems to be alias, It would be good to know which elements / attributes are you referencing from these alias.

    USE tempdb;
    GO
    DECLARE @x xml;
    
    SET @x = '<OrdersWithTheirCopies>
     <Order OrderID="1" OrderNumber="Order #1">
     <OrderLines>
     <OrderLine OrderLineID="1" LineNumber="Line #1" ItemDescription="pipe - 100" />
     <OrderLine OrderLineID="2" LineNumber="Line #2" ItemDescription="plate 10" />
     <OrderLine OrderLineID="3" LineNumber="Line #3" ItemDescription="glass 1 mm." />
     </OrderLines>
     <OrderCopies>
     <Copy CopyNumber="1" OrderHistoryID="3">
     <OrderLineHistory OrderLineID="1" OrderLineHistoryID="8" ItemDescription="pipe - 100" />
     <OrderLineHistory OrderLineID="2" OrderLineHistoryID="9" ItemDescription="plate 10" />
     <OrderLineHistory OrderLineID="3" OrderLineHistoryID="10" ItemDescription="glass 1 mm." />
     </Copy>
     <Copy CopyNumber="2" OrderHistoryID="4">
     <OrderLineHistory OrderLineID="1" OrderLineHistoryID="11" ItemDescription="pipe - 100" />
     <OrderLineHistory OrderLineID="2" OrderLineHistoryID="12" ItemDescription="plate 10" />
     <OrderLineHistory OrderLineID="3" OrderLineHistoryID="13" ItemDescription="glass 1 mm." />
     </Copy>
     <Copy CopyNumber="3" OrderHistoryID="6">
     <OrderLineHistory OrderLineID="1" OrderLineHistoryID="16" ItemDescription="pipe - 100" />
     <OrderLineHistory OrderLineID="2" OrderLineHistoryID="17" ItemDescription="plate 10" />
     <OrderLineHistory OrderLineID="3" OrderLineHistoryID="18" ItemDescription="glass 1 mm." />
     </Copy>
     </OrderCopies>
     </Order>
     <Order OrderID="2" OrderNumber="Order #2">
     <OrderLines>
     <OrderLine OrderLineID="4" LineNumber="Line #1" ItemDescription="pipe - 200" />
     <OrderLine OrderLineID="5" LineNumber="Line #2" ItemDescription="plate 20" />
     </OrderLines>
     <OrderCopies>
     <Copy CopyNumber="1" OrderHistoryID="2">
     <OrderLineHistory OrderLineID="4" OrderLineHistoryID="6" ItemDescription="pipe - 200" />
     <OrderLineHistory OrderLineID="5" OrderLineHistoryID="7" ItemDescription="plate 20" />
     </Copy>
     <Copy CopyNumber="2" OrderHistoryID="5">
     <OrderLineHistory OrderLineID="4" OrderLineHistoryID="14" ItemDescription="pipe - 200" />
     <OrderLineHistory OrderLineID="5" OrderLineHistoryID="15" ItemDescription="plate 20" />
     </Copy>
     </OrderCopies>
     </Order>
     <Order OrderID="3" OrderNumber="Order #3">
     <OrderLines>
     <OrderLine OrderLineID="6" LineNumber="Line #1" ItemDescription="pipe - 300" />
     <OrderLine OrderLineID="7" LineNumber="Line #2" ItemDescription="plate - 30" />
     <OrderLine OrderLineID="8" LineNumber="Line #3" ItemDescription="glass - 3 mm" />
     <OrderLine OrderLineID="9" LineNumber="Line #4" ItemDescription="pipe - 330" />
     <OrderLine OrderLineID="10" LineNumber="Line #5" ItemDescription="plate - 33" />
     </OrderLines>
     <OrderCopies>
     <Copy CopyNumber="1" OrderHistoryID="1">
     <OrderLineHistory OrderLineID="6" OrderLineHistoryID="1" ItemDescription="pipe - 300" />
     <OrderLineHistory OrderLineID="7" OrderLineHistoryID="2" ItemDescription="plate - 30" />
     <OrderLineHistory OrderLineID="8" OrderLineHistoryID="3" ItemDescription="glass - 3 mm" />
     <OrderLineHistory OrderLineID="9" OrderLineHistoryID="4" ItemDescription="pipe - 330" />
     <OrderLineHistory OrderLineID="10" OrderLineHistoryID="5" ItemDescription="plate - 33" />
     </Copy>
     </OrderCopies>
     </Order>
    </OrdersWithTheirCopies>';
    
    SELECT
    	O.x.value('@OrderID[1]', 'int') AS OrderID,
    	O.x.value('@OrderNumber[1]', 'varchar(50)') AS OrderNumber
    FROM
    	@x.nodes('/OrdersWithTheirCopies/Order') AS O(x);
    
    SELECT
    	C.x.value('@OrderHistoryID[1]', 'int') AS OrderHistoryID,
    	O.x.value('@OrderID[1]', 'int') AS OrderID,
    	C.x.value('@CopyNumber[1]', 'int') AS CopyNumber
    FROM
      @x.nodes('/OrdersWithTheirCopies/Order') AS O(x)
      CROSS APPLY
      O.x.nodes('OrderCopies/Copy') AS C(x);
    
    SELECT
    	L.x.value('@OrderLineHistoryID[1]', 'int') AS OrderLineHistoryID,
    	O.x.value('@OrderID[1]', 'int') AS OrderID,
    	L.x.value('@OrderLineID[1]', 'int') AS OrderLineID,
    	L.x.value('@ItemDescription[1]', 'varchar(max)') AS ItemDescription
    FROM
      @x.nodes('/OrdersWithTheirCopies/Order') AS O(x)
      CROSS APPLY
      O.x.nodes('OrderCopies/Copy') AS C(x)
      CROSS APPLY
      C.x.nodes('OrderLineHistory') AS L(x);
    GO


    AMB

    Some guidelines for posting questions...

    • Marked as answer by SunnyIngulia Monday, November 1, 2010 4:20 PM
    Monday, November 1, 2010 4:17 PM
    Moderator
  • Monday, November 1, 2010 5:47 PM
  • wBob,

    Thanks for reminding me about it.

    If anybody feel like this issue should be fixed or improved in future releases, then I suggest to give your vote to this entry in the suggestion / bug report site.

    Parent navigations in .value results in absymal performance.

     


    AMB

    Some guidelines for posting questions...

    Monday, November 1, 2010 6:24 PM
    Moderator