Insert the xml data into the sql table in stored procedure

Question

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)

possibly the duplicate of this : https://stackoverflow.com/questions/50562028/insert-xml-data-to-table-stored-procedure-sql-server — Mahavir Kumbharvadia, Apr 10 '19 at 07:21
No that is different i tried like i used two CROSS APPLY functions then it is looping two times... — Nayeem Shaiky, Apr 10 '19 at 07:23
It is good if you can put your sql query so it will give more clarification as what is exactly wrong in it. — Mahavir Kumbharvadia, Apr 10 '19 at 07:28
XML is hard to work with using SQL Server. I find using another programming language to read xml and stored into database is easier to do. — jdweng, Apr 10 '19 at 08:56
Its ok no problem as of now. do you have solution for this..? — Nayeem Shaiky, Apr 10 '19 at 08:59

Chris · Accepted Answer · 2019-04-10T09:23:05.030

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

If that's truely your xml schema you can do it with the edited code, it's assuming there is a name for each amount and that the order they appear in the xml is the relationship between name and amount — Chris, Apr 10 '19 at 09:22
I've updated my answer to handle multiple groupings inside of an entity — Chris, Apr 10 '19 at 09:25

Insert the xml data into the sql table in stored procedure

1 Answers1