CODEWITHSUNDEEP
sqlsql-serverxmljoin
kushal
kushal

Reputation: 181

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

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:

enter image description here

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

enter image description here

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)');

Upvotes: 0

Views: 147

Answers (1)

Thom A
Thom A

Reputation: 96055

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.

Upvotes: 3

Related Questions