Click here to Skip to main content
15,071,729 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
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
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

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)') ));
   
Comments
Mathi2code 16-Apr-14 9:39am
   
Hi Andrius,

Yes, I need something like this but I need to have this as dynamic. Since the XML structure will be dynamic. Meaning I cannot say column names in where condition like this.

Thanks & Regards,
Mathi.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900