Avoid double XML INSERT to SQL

Question

I need to import XML data into SQL Server 2012. The import works correctly, but I would want to avoid double import. I already tried with WHERE NOT EXISTS but it didn't work.

The import:

INSERT INTO dbo.tXMLImport(cText)
SELECT cast(CONVERT(XML,x.BulkColumn,2) AS varchar(max))
FROM OPENROWSET (BULK 'D:\XML\Data.xml', SINGLE_BLOB) AS x

EXL file content:

<?xml version="1.0" encoding="UTF-8"?>
<tOrder>
    <cName>Name1</cName>
    <cID>100</cID>
</tOrder>

Now, it should be checked if cID value 100 from XML file already exist in

dbo.tOrder row cOrderNumber

    cOrderNumber
1   100
2   101
3   102

Following extention does not wokr:

WHERE NOT EXISTS(SELECT * 
                FROM dbo.tOrder 
                WHERE x.value('(/tOrder/cID)') = dbo.tOrder.CorderNumber)

If yes, no Import to be done. Maybe some one can support me with? Thanks in advance.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

I'm not sure if I really get this... If the same cOrderNumber exists already, wouldn't you try to update the existing row? Something like you'd do with MERGE?

But It might be something like this what you are looking for:

WHERE NOT EXISTS(SELECT 1 FROM dbo.tOrder
                 WHERE x.exist(N'/tOrder[cID/text()=sql:column("cOrderNumber")])')=1)

(Untested air code)

This looks if there is any record within tOrder where the XML column x has any occurance of a node <tOrder><CID> with a value like the current cOrderNumber's value.

T-SQL adds the sql:column() method to XQuery, which allows to use the value of a row within the query. There's sql:variable() too.

The xml's method .exist() checks the XML for any existance of a given condition and returns with 0 or 1.

UPDATE

After reading your question once again, I'm not sure if I got this correctly... Please check the following. If this doesn't help, please use my code to set up a stand-alone sample to reprodcue your issue:

A dummy table with some orders

DECLARE @YourTable TABLE(cOrderNumber INT, OrderName VARCHAR(100));
INSERT INTO @YourTable VALUES
 (100,'Order 100')
,(200,'Order 200')
,(300,'Order 300')

--Try to insert an XML with the existing OrderNumber=100

DECLARE @xml100 XML=
'<tOrder>
    <cName>Name1</cName>
    <cID>100</cID>
</tOrder>';

INSERT INTO @YourTable(cOrderNumber,OrderName)
SELECT @xml100.value('(/tOrder/cID/text())[1]','int')
      ,@xml100.value('(/tOrder/cName/text())[1]','varchar(100)')
WHERE NOT EXISTS(SELECT 1 FROM @YourTable AS t2 
                 WHERE [email protected]('(/tOrder/cID/text())[1]','int'));

--Same code as above, but the order number is now a not existing number

DECLARE @xml101 XML=
'<tOrder>
    <cName>Name1</cName>
    <cID>101</cID>
</tOrder>';

INSERT INTO @YourTable(cOrderNumber,OrderName)
SELECT @xml101.value('(/tOrder/cID/text())[1]','int')
      ,@xml101.value('(/tOrder/cName/text())[1]','varchar(100)')
WHERE NOT EXISTS(SELECT 1 FROM @YourTable AS t2 
                 WHERE [email protected]('(/tOrder/cID/text())[1]','int'));

--check the result

SELECT *
FROM @YourTable;

nr  name
-------------
100 Order 100
200 Order 200
300 Order 300
101 Name1

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 5, 2017 at 18:06

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Schiell Over a year ago

Tanks a lot for support Shnugo. I don't need to update cOrderNumber, because XML import with ID 100* should be done only ones. It is only to avoid duplicates, when for example xml file going to be imported twice by mistake. Unfortunately, there is an error message: "A non-Boolean expression was specified in a context where a condition is expected." Actually it shouldn't be **WHERE NOT EXISTS Maybe there is another way to solve this issue? Thanks in advance.

Gottfried Lesigang Over a year ago

@Schiell, There was a typo, sorry for that! The =1 checking the result of .exist() was part of the XQuery, but must be outside of course. This is quite probably the reason for the non-boolean expression error. But check my updated answer, there's an alternative approach too

Collectives™ on Stack Overflow

Avoid double XML INSERT to SQL

1 Answer 1

UPDATE

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related