CODEWITHSUNDEEP
sql-serverxml
Nayeem Shaiky
Nayeem Shaiky

Reputation: 101

Insert the xml data into the sql table in stored procedure

I want to insert the XML data to sql table in stored procedure. I want to insert CNAME and AMOUNT values. How to achieve this with below xml?

<ENTITY>
<CNAME>
<NAME>FGGFGFGF</NAME>
</CNAME>
<AMOUNT>
<AMOUNT1></AMOUNT1>
<AMOUNT2>67052549.33</AMOUNT2>
</AMOUNT>
<CNAME>
<NAME>FGGFGFGF</NAME>
</CNAME>
<AMOUNT>
<AMOUNT1></AMOUNT1>
<AMOUNT2>67052549.33</AMOUNT2>
</AMOUNT>
</ENTITY> 
INSERT INTO Table1(NAME,AMOUNT1, AMOUNT2) SELECT t.c.value('(NAME)[1]', 'varchar(50)') AS NAME, d.c.value('(AMOUNT1) 
[1]', 'varchar(50)') AS AMOUNT1, d.c.value('(AMOUNT2)[1]','varchar(50)') as 
AMOUNT2 FROM @xmlData.nodes('/ENTITY') m(c) CROSS APPLY m.c.nodes(N'CNAME') 
t(c) CROSS APPLY m.c.nodes(N'AMOUNT') d(c)

Upvotes: 0

Views: 344

Answers (1)

Chris
Chris

Reputation: 129

This should get you the result you're after, be sure to check the datatypes 

DECLARE @XML AS XML= N'
<ENTITY> 

<CNAME> 
<NAME>FGGFGFGF</NAME> 
</CNAME> 
<AMOUNT> 
<AMOUNT1></AMOUNT1> 
<AMOUNT2>67052549.33</AMOUNT2> 
</AMOUNT> 

<CNAME> 
<NAME>FGGFGFGF</NAME> 
</CNAME> 
<AMOUNT> 
<AMOUNT1>
</AMOUNT1> 
<AMOUNT2>67052549.33</AMOUNT2> 
</AMOUNT>

</ENTITY> '
SELECT   nme.ROWID
        ,nme.NAME
        ,amt.AMOUNT1
        ,amt.AMOUNT2
FROM 
    (
        SELECT   TRY_CONVERT(NVARCHAR(50),nme.value('(NAME/text())[1]',       'VARCHAR(MAX)'))      AS NAME
                ,ROW_NUMBER() OVER (ORDER BY T.nme)                                                 AS ROWID    
        FROM    @XML.nodes('ENTITY/CNAME') AS T(nme) 
    )nme
    LEFT OUTER JOIN
    (
        SELECT   TRY_CONVERT(MONEY,amt.value('(AMOUNT1/text())[1]',           'VARCHAR(MAX)'))      AS AMOUNT1
                ,TRY_CONVERT(MONEY,amt.value('(AMOUNT2/text())[1]',           'VARCHAR(MAX)'))      AS AMOUNT2
                ,ROW_NUMBER() OVER (ORDER BY T.amt)                                                 AS ROWID    
        FROM    @XML.nodes('ENTITY/AMOUNT') AS T(amt) 
    )amt
        ON nme.ROWID = amt.ROWID
OPTION (OPTIMIZE FOR (@XML = NULL))

Upvotes: 1

Related Questions