SQL Server – How to Load XML Child Nodes into Multiple Tables

sql serverxml

I would like to load the below XML into 2 tables

Table 1: Acct Table – it should have the ACCT_NUM and a Key to tie to the Table 2 below

Table 2: Should have all the Contacts (1 record per Contact_Type) and should be able to link back to Table 1

Here is my XML:

<?xml version="1.0" encoding="UTF-8"?>
<Extract>
<Request>
<ACCT_NUM>111111</ACCT_NUM>
<Data>
<Contacts>
<Contact CONTACT_TYPE="CRM">
<CONTACT_TOLL_FREE>800.123.4444</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 11111</CONTACT_ADDRESS_LINE_1>
</Contact>
<Contact CONTACT_TYPE="Collections">
<CONTACT_TOLL_FREE>800.123.5555</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 22222</CONTACT_ADDRESS_LINE_1>
</Contact>
</Contacts>
</Request>
<Request>
<ACCT_NUM>222222</ACCT_NUM>
<Data>
<Contacts>
<Contact CONTACT_TYPE="CRM">
<CONTACT_TOLL_FREE>888.123.6666</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 33333</CONTACT_ADDRESS_LINE_1>
</Contact>
<Contact CONTACT_TYPE="Collections">
<CONTACT_TOLL_FREE>888.123.7777</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 44444</CONTACT_ADDRESS_LINE_1>
</Contact>
</Contacts>
</Request>
</Extract>

Any help is greatly appreciated.

Best Answer

As I pointed out in my comment there is a missing </Data> tag in your example. But if the file has it, you can use the next command to extract XML values:

SELECT 
    t2.lin.value('(./ACCT_NUM)[1]', 'int') as ACCT_NUM
    ,t3.lin.value('(./@CONTACT_TYPE)[1]', 'varchar(100)') as CONTACT_TYPE
    ,t3.lin.value('(./CONTACT_TOLL_FREE)[1]', 'varchar(100)') as CONTACT_TOLL_FREE
    ,t3.lin.value('(./CONTACT_ADDRESS_LINE_1)[1]', 'varchar(100)') as CONTACT_ADDRESS_LINE_1
    INTO #tmp
FROM
    txml
CROSS APPLY 
    data.nodes('Extract/Request') as t2(lin)
CROSS APPLY
    t2.lin.nodes('Data/Contacts/Contact') as t3(lin);

NOTE: I've used a temporary table #tmp to store the returned values.

Then you can use this values to build/insert that values into the two tables:

SELECT 
    ACCT_NUM
INTO
    #table1
FROM #tmp;

SELECT 
    ACCT_NUM,
    CONTACT_TYPE,
    CONTACT_TOLL_FREE,
    CONTACT_ADDRESS_LINE_1
INTO
    #table2
FROM #tmp;

And this is the final result:

SELECT * FROM #table1;

SELECT * FROM #table2;

| ACCT_NUM |
| -------: |
|   111111 |
|   111111 |
|   222222 |
|   222222 |

ACCT_NUM | CONTACT_TYPE | CONTACT_TOLL_FREE | CONTACT_ADDRESS_LINE_1
-------: | :----------- | :---------------- | :---------------------
  111111 | CRM          | 800.123.4444      | PO Box 11111          
  111111 | Collections  | 800.123.5555      | PO Box 22222          
  222222 | CRM          | 888.123.6666      | PO Box 33333          
  222222 | Collections  | 888.123.7777      | PO Box 44444

db<>fiddle here

Related Question