I have one table (say messages
) with primary_key
and xml_data
. I know how to extract several values from one xml_data
value, like this:
DECLARE @x XML = 'the XML value'
;WITH XMLNAMESPACES (
'http://somenamespace/document' AS msg,
'http://somenamespace/externalMessage' AS dat
)
SELECT c.value('(dat:delivery_list/dat:identifier)[1]', 'varchar(20)') AS dl_identifier,
c.value('(dat:created_on)[1]', 'datetime2(0)') AS created_on,
c.value('(dat:delivery_list/dat:shipping_date)[1]', 'datetime2(0)') AS shipping_date
FROM @x.nodes('/msg:data/msg:body/msg:request/msg:data/dat:operation/dat:data/dat:external_message') AS t(c)
Now, I want to build another table (say heads
), with the same primary_key
and with the columns dl_identifier
, created_on
, and shipping_date
filled with the extracted information from the messages.xml_data
. It should process only the messages.xml_data
that are new (no extraction was done, yet).
So far, I was thinking about left-outer joining the tables to detect the missing heads
records like this:
SELECT m.primary_key,
m.xml_data
FROM messages AS m
LEFT OUTER JOIN heads AS h
ON h.primary_key = m.primary_key
AND m.xml_data IS NOT NULL
WHERE h.primary_key IS NULL
It selects the wanted m.xml_data
. Is it possible to insert the values extracted from the m.xml_data
(and with the primary_key
) into heads
without using a cursor?
It would help most to provide an MCVE. Look at the following example:
--a mockup table to simulate your messages
DECLARE @tblMessages TABLE (ID INT IDENTITY PRIMARY KEY, xml_data XML);
--I insert 4 messages
INSERT INTO @tblMessages VALUES ('<root><test a="test 1.1" /><test a="test 1.2" /></root>')
,('<root><test a="test 2.1" /><test a="test 2.2" /></root>')
,('<root><test a="test 3.1" /></root>')
,('<root><test a="test 4.1" /><test a="test 4.2" /><test a="test 4.3" /></root>');
--a mockup table to simulate your "heads" table
DECLARE @tblExtractedValues TABLE(ID INT IDENTITY PRIMARY KEY
,message_ID INT NOT NULL /*always use names constaints and you might add FOREIGN KEY REFERENCES messages(primary_key)*/
,ExtractedValue_test VARCHAR(100));
--Let's simulate, that messages 1 and 3 are extracted already
INSERT INTO @tblExtractedValues VALUES(1,'test 1.1')
,(1,'test 1.2')
,(3,'test 3.1');
--This statement will read the values out of the XML in "messages",
--but will omit those with an ID existing in the other table
--You can out-comment the line with INSERT
to see the SELECT's result
INSERT INTO @tblExtractedValues(message_ID,ExtractedValue_test)
SELECT m.ID
,t.value('@a','varchar(100)')
FROM @tblMessages m
CROSS APPLY m.xml_data.nodes('/root/test') A(t)
WHERE NOT EXISTS(SELECT 1
FROM @tblExtractedValues ev
WHERE ev.message_ID=m.ID);
--Look at the order of insertion. 1 and 3 have lower IDs than 2 and 4
SELECT * FROM @tblExtractedValues;