How to Load XML child nodes in to multiple tables 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:

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


Any help is greatly appreciated.
As I pointed out in my comment there is a missing &lt;/Data&gt; 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&lt;>fiddle here