Search code examples
xmlt-sqlsql-server-2014

How to insert fields extracted from XML in one table into another table?


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?


Solution

  • 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;