How to Merge XML variable to a SQL Table in a stored procedure

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi,

I have an XML Variable passed as parameter to SP.
I wanted to merge that XML with the SQL Table.

I just had a thought to create a dynamic view from the XML variable and from that view I can merge to the original SQL Table.
Is this possible ?

Else there is a way to directly merge the XML variable to SQL Table.

For Example:

I have a XML variable whose value is as below:

DECLARE @XMLDoc xml = '

<NewDataSet>
  <Test_Reports>
    <ByteArray>1</ByteArray>
    <ColumnTest>A</ColumnTest>
    <ColumnTest2>B</ColumnTest2>
    <T>TestValue</T>
  </Test_Reports>
</NewDataSet>

',

I wanted to merge with the table Test_Reports with the same Column structure.
If this data doen't exist then insert into the table.

SQL

CREATE TABLE [dbo].[Test_Reports](
    [ByteArray] [int] NULL,
    [ColumnTest] [varchar](50) NULL,
    [ColumnTest2] [varchar](50) NULL
) ON [PRIMARY]

Please help me in this.

Thanks & Regards,
Mathi.

Posted 15-Apr-14 5:36am

Mathi2code

Add a Solution

Comments

Abinash_Sahoo 15-Apr-14 11:54am

it seems the xml has been created from a dataset. so you can load the xml into your dataset and inset it in the table!

Mathi2code 16-Apr-14 6:42am

Hi Abinash,

Yes it is getting generated from a DataSet.
If I can insert from DataSet to Table is there an option to Merge data.
Meaning If the entry is already there update else insert.

Thanks & Regards,
Mathi

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Answer 1 · 2014-04-15T07:03:00

Hi,

Do you want to do something like this?

Query:

SQL

INSERT INTO [dbo].[Test_Reports]
            ([ByteArray],
             [ColumnTest],
             [ColumnTest2])
SELECT x.Test_Reports.value('(ByteArray)[1]', 'int'),
       x.Test_Reports.value('(ColumnTest)[1]', 'varchar(50)'),
       x.Test_Reports.value('(ColumnTest2)[1]', 'varchar(50)')
FROM   @XMLDoc.nodes('/NewDataSet/Test_Reports') AS x(Test_Reports)
WHERE  NOT EXISTS (SELECT 1
                   FROM   [dbo].[Test_Reports]
                   WHERE  ( [ByteArray] = x.Test_Reports.value('(ByteArray)[1]', 'int') )
                          AND ( [ColumnTest] = x.Test_Reports.value('(ColumnTest)[1]', 'varchar(50)') )
                          AND ( [ColumnTest2] = x.Test_Reports.value('(ColumnTest2)[1]', 'varchar(50)') ));

How to Merge XML variable to a SQL Table in a stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

How to Merge XML variable to a SQL Table in a stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us