Problem reading xml nodes to a table

Brian collins 141 Reputation points
2021-01-22T20:49:51.893+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,937 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,566 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,211 Reputation points
    2021-01-22T21:25:54.537+00:00

    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 |
    +-------------+---------+
    
    0 comments No comments

0 additional answers

Sort by: Most helpful