0

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.

3

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

  • McNets - What is the txml in your SELECT statement. I fixed the xml (sorry about that) but I am not able to get your select into #temp SQL execute a it is erroring (Err: Invalid object name 'txml'.) – Deepak Aug 26 at 19:07
  • If you follow the db fifdle link, you'll see the whole example, I've created a table to hold the XML text. – McNets Aug 26 at 19:22
  • Perfect - exactly what I was looking for. Thank you so much for your help. – Deepak Aug 26 at 19:31
  • I'm glad to help – McNets Aug 26 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.