SQL query to join table with XML element and get nested element properties

Question

I have XML like below

<Parent>  
    <BOMLine> 
    **<partnumber>abc</partnumber>**
    <SortOrder>0</SortOrder> 
    <Component>
      <PartNo>SE3008</PartNo>
      <Comp>12P6383X012</Comp>
      <CompDesc>SQ Controller</CompDesc>
      <Qty>1</Qty>
    </Component>
    <ChangeOrder>
      <Qty>10</Qty>
      <COId>AS</COId>
      <IsQtyLinked>false</IsQtyLinked>
    </ChangeOrder> 
    <ChangeOrder>
      <Qty>10</Qty>
      <COId>AS1</COId>
      <IsQtyLinked>true</IsQtyLinked>
    </ChangeOrder>
  </BOMLine>
  <BOMLine> ....
</Parent>

and a temp table like this:

I want to join the xml and table on PartNumber properties and need the following output

And I don't want to use doc handle to handle xml,

Please help me to get the desired output.

I have tried following code, but I am confused how to get nested element from XML while joining.

DECLARE @TempTable TABLE (ID INT,
                          partnumber VARCHAR(200),
                          sortnumber INT)

INSERT INTO @TempTable (ID, partnumber, sortnumber) 
VALUES (123, 'abc', 1)



  DECLARE @xml XML ='<Parent>  
        <BOMLine> 
        <partnumber>abc</partnumber>
        <SortOrder>0</SortOrder> 
        <Component>
          <PartNo>SE3008</PartNo>
          <Comp>12P6383X012</Comp>
          <CompDesc>SQ Controller</CompDesc>
          <Qty>1</Qty>
        </Component>
        <ChangeOrder>
          <Qty>10</Qty>
          <COId>AS</COId>
          <IsQtyLinked>false</IsQtyLinked>
        </ChangeOrder> 
        <ChangeOrder>
          <Qty>10</Qty>
          <COId>AS1</COId>
          <IsQtyLinked>true</IsQtyLinked>
        </ChangeOrder>
      </BOMLine> 

     <BOMLine> 
        <partnumber>abc</partnumber>
        <SortOrder>0</SortOrder> 
        <Component>
          <PartNo>SE3008</PartNo>
          <Comp>12P6383X012</Comp>
          <CompDesc>SQ Controller</CompDesc>
          <Qty>1</Qty>
        </Component>
        <ChangeOrder>
          <Qty>10</Qty>
          <COId>AS</COId>
          <IsQtyLinked>false</IsQtyLinked>
        </ChangeOrder> 
        <ChangeOrder>
          <Qty>10</Qty>
          <COId>AS1</COId>
          <IsQtyLinked>true</IsQtyLinked>
        </ChangeOrder>
      </BOMLine> 
    </Parent> '




SELECT  TT.ID,
       TT.partnumber,
       B.CO.value('(Qty/text())[1]','int') AS Qty,
       B.CO.value('(COId/text())[1]','char(2)') AS COId,
       B.CO.value('(IsQtyLinked/text())[1]','varchar(5)') AS IsLinked
FROM @TempTable TT  
     CROSS APPLY @xml.nodes('Parent/BOMLine') P(BOM)
     CROSS APPLY P.BOM.nodes('./ChangeOrder') B(CO)
WHERE TT.partnumber = P.BOM.value('(partnumber/text())[1]','varchar(3)');

What have you tried so far, and why didn't it work? Where is the XML stored, in another table, a variable, other? — Thom A
– Thom A ♦, Commented May 3, 2019 at 11:20
i tried but i am confuse how to get nested node properties, due to this i have posted this question — kushal
– kushal, Commented May 3, 2019 at 11:22
If you post your SQL then we can see where you went wrong. Without it, it's vrry difficult (impssible) for us to debug your code. Please edit your question with your attempt(s) so that we can help you. If you got any errors, post those too. Thanks. — Thom A
– Thom A ♦, Commented May 3, 2019 at 11:26

Thom A · Accepted Answer · 2019-05-03 11:58:59Z

3

You were close. This is the way I would do it with the sample data you've supplied:

SELECT TT.ID,
       TT.partnumber,
       B.CO.value('(Qty/text())[1]','int') AS Qty,
       B.CO.value('(COId/text())[1]','char(2)') AS COId,
       B.CO.value('(IsQtyLinked/text())[1]','varchar(5)') AS IsLinked
FROM @TempTable TT
     CROSS APPLY @xml.nodes('Parent/BOMLine') P(BOM)
     CROSS APPLY P.BOM.nodes('./ChangeOrder') B(CO)
WHERE TT.partnumber = P.BOM.value('(partnumber/text())[1]','varchar(3)');

Note I use nodes twice, once to get a row for each BOMLine, another for each ChangeOrder. I then use the WHERE to create an implicit JOIN to the table (@TempTable).

As this is sample data, note you may need to change the return data types from the XML to something more appropriate for your real data.

edited May 3, 2019 at 11:58

answered May 3, 2019 at 11:53

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

kushal Over a year ago

not working with multiple <BOMLine> tag under parent node

Thom A Over a year ago

It will, @kushal, it uses CROSS APPLY @xml.nodes('Parent/BOMLine') P(BOM); which generates a new rowset for every BomLine node under Parent. Your sample data only contains one BOMLine node though, so that's all I tested against. if you aren't getting multiple lines, that implies that something else is different; but i can't see your data so I have no way of knowing. you'll need to update your question if so.

kushal Over a year ago

I have updated XML please use above xml and execute query,

kushal Over a year ago

and insert 1 record into temp table , INSERT INTO @TempTable (ID, partnumber, sortnumber) VALUES (124, 'abc1', 1)

Thom A Over a year ago

I get 4 rows there @kushal, which is as you expect: db<>fiddle

|

Collectives™ on Stack Overflow

SQL query to join table with XML element and get nested element properties

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related