Problem reading xml nodes to a table

Question

Hi,

I'm having issue reading all the ModelID's with respect to the Model. How do I get all ModelID's into the table w.r.t the CompanyCode

DECLARE @XmlData XML
SET @XmlData = '<Product>
 <Model CompanyCode="3020">
 <ModelID>843743</ModelID>   
 <ModelID>253463</ModelID>   
 </Model>   
 <Model CompanyCode="3021">     
 <ModelID>343253</ModelID>   
 <ModelID>254262</ModelID>   
 <ModelID>007007</ModelID>   
 </Model> 
 </Product>'

DECLARE @Temp Table(tModel VARCHAR(50), ModelID Varchar(255))
INSERT INTO @Temp
SELECT T.v.value('./@CompanyCode[1]', 'NVARCHAR(10)') AS CompanyCode,
 T.v.value('./ModelID[1]', 'NVARCHAR(10)') AS ModelID
FROM @XmlData.nodes('/Product/Model') AS T(v)

Select * FROM @Temp

Answer 1

Here is how to do it.

SQL

DECLARE @XmlData XML = 
N'<Product>
    <Model CompanyCode="3020">
        <ModelID>843743</ModelID>
        <ModelID>253463</ModelID>
    </Model>
    <Model CompanyCode="3021">
        <ModelID>343253</ModelID>
        <ModelID>254262</ModelID>
        <ModelID>007007</ModelID>
    </Model>
</Product>';

SELECT p.value('@CompanyCode', 'NVARCHAR(10)') AS CompanyCode
    , c.value('(./text())[1]', 'NVARCHAR(10)') AS ModelID
FROM @XmlData.nodes('/Product/Model') AS t1(p)
    CROSS APPLY t1.p.nodes('ModelID') AS t2(c);

Output

+-------------+---------+
| CompanyCode | ModelID |
+-------------+---------+
|        3020 |  843743 |
|        3020 |  253463 |
|        3021 |  343253 |
|        3021 |  254262 |
|        3021 |  007007 |
+-------------+---------+