clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1044238 fiddles created (9448 in the last week).

create table txml(id int identity, data xml); insert into txml (data) values (' <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> </Data> </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> </Data> </Request> </Extract>');
1 rows affected
 hidden batch(es)


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); SELECT ACCT_NUM INTO #table1 FROM #tmp; SELECT ACCT_NUM, CONTACT_TYPE, CONTACT_TOLL_FREE, CONTACT_ADDRESS_LINE_1 INTO #table2 FROM #tmp; 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
 hidden batch(es)