Import XML into MS SQL Server 2012

Hi,

I am looking for a way to load xml files into MS SQL Server 2012.

The xml I am working with is structured as shown below.  It is transaction information from a grocery store point of sale system.

Each 'trs' element has various attributes that could go into an 'ORDER_HEADER' table.

Each child 'r' element contains line items for each 'trs' record, and could go into an 'ORDER_DETAILS' table.  

The 'key' elements would need to into their own table 'KEY_STROKES' using a primary key comprised of  F1056, F1057, F1032 from the 'trs' element, and also F1101 from its parent 'r' element.

The 't' elements would also need to go into their own table 'TOTALIZERS',  using the following 'trs' elements as its primary key (F1056, F1057, F1032)

Thanks for your help with this,

Ariel





DECLARE @XML XML =
<?xml version="1.0" encoding="iso_8859-1"?>
<day xmlns="x-schema:..\schema_ej.xml" FILE="50716049.002">
	
	<trs F1068="SALE" F254="2015-07-16" F253="2015-07-16" F1056="049" F1057="002" F1035="12:35:04" 
		F1036="12:35:47" F1032="6063" F1764="00006036" F1185="20" F1126="498494" F1127="Corrinap" 
		F1148="40101708114395" F1155="LINDA MAHELONA" F1152="2">
			<r F1101="1">
				<itm F01="1000000164" F02="FRESH MEX BURRITO" F04="151" F03="5" F81="1" F1007="7.99" F1006="1" F1080="4.08"/>
				<F65>15.98</F65>
				<F64>2</F64>
				<F1263>0.75</F1263>
				<exc F1511="3401" F1512="Points 1" F1513="TLZ" F1514="1" F1515="16"/>
				<key fn="30"/>
				<key in="0" fn="10011"/>
				<key in="3" fn="720"/>
				<key in="ERROR" fn="30"/>
				<key fn="10011"/>
				<key in="498494" fn="10011"/>
				<key in="8494" fn="10011"/>
				<key in="40101708114395" fn="307"/>
				<key in="2204" fn="710"/>
				<key fn="40"/>
				<key in="2" fn="40"/>
			</r>
			<r F1101="2">
				<itm F01="1000000175" F02="VEGGIE DOG" F04="151" F03="5" F81="1" F1007="4.99" F1006="1" F1080="6.401"/>
				<F65>4.99</F65>
				<F64>1</F64>
				<F1263>0.24</F1263>
				<exc F1511="3401" F1512="Points 1" F1513="TLZ" F1514="1" F1515="5"/>
				<key in="221" fn="710"/>
				<key in="ERROR" fn="30"/>
				<key in="2215" fn="710"/>
			</r>
			<r F1101="3">
				<itm F01="1000035102" F02="SMOOTHIE CREAMY PINA CLDA" F04="21" F03="5" F81="1" F79="1" F1007="9.99" F1006="1" F1080="2.748"/>
				<F65>9.99</F65>
				<F64>1</F64>
				<F1263>0.47</F1263>
				<exc F1511="3401" F1512="Points 1" F1513="TLZ" F1514="1" F1515="10"/>
				<key in="2976" fn="710"/>
			</r>
			<r F1101="4">
				<itm F01="1000058869" F02="POT CHP SALT &amp; VINEGAR" F04="51" F03="9" F81="1" F79="1" F113="STOR" F1007="5.59" F1006="1" F1080="2.079"/>
				<F65>5.59</F65>
				<F64>1</F64>
				<F1263>0.26</F1263>
				<exc F1511="3401" F1512="Points 1" F1513="TLZ" F1514="1" F1515="6"/>
				<key in="856823004028" fn="710"/>
			</r>
			<r F1101="5">
				<fct F1063="131" F02="MasterCard" F113="" F1080="18.496"/>
				<F65>38.27</F65>
				<F64>1</F64>
				<eft now="2015-07-16" typ="CC" acc="************3754=****" ent="S" aut="02813J" ref="004279" bat="20150716" name="RB" term="463733" tcode="0"/>
				<key fn="930"/>
				<key fn="720"/>
				<key fn="190"/>
				<key fn="30"/>
				<key fn="104"/>
			</r>
			<t><tlz F1034="2" F02="TOTAL SALES"/>
				<F65>38.27</F65>
				<F64>1</F64>
			</t>
			<t><tlz F1034="3" F02="Net Sales"/>
				<F65>36.55</F65>
				<F64>5</F64>
			</t>
			<t><tlz F1034="7" F02="Discountable Sales"/>
				<F65>36.55</F65>
				<F64>5</F64>
			</t>
			<t><tlz F1034="21" F02="Taxable 1"/>
				<F65>36.55</F65>
				<F64>5</F64>
			</t>
			<t><tlz F1034="22" F02="Tax 1"/>
				<F65>1.72</F65>
			</t>
			<t><tlz F1034="90" F02="Food stampable"/>
				<F65>15.58</F65>
				<F64>2</F64>
			</t>
			<t><tlz F1034="1231" F02="MasterCard"/>
				<F65>38.27</F65>
				<F64>1</F64>
			</t>
			<t><tlz F1034="3401" F02="Points given"/>
				<F65>0.00</F65>
				<F64>37</F64>
			</t>
			<t><tlz F1034="3415" F02="Admissible spending"/>
				<F65>36.55</F65>
				<F64>5</F64>
			</t>
			<t><tlz F1034="3650" F02="Temporary markdown"/>
				<F65>0.50</F65>
				<F64>1</F64>
			</t>
			<t><tlz F1034="7501" F02="Customer balance current"/>
				<F65>0.00</F65>
			</t>
			<t><tlz F1034="7531" F02="Saving grand total"/>
				<F65>0.50</F65>
				<F64>1</F64>
			</t>
			<t><tlz F1034="7901" F02="Points 1 balance"/>
				<F65>0.00</F65>
				<F64>121</F64>
			</t>
			<t><tlz F1034="29999" F02="BALANCE"/>
				<F65>0.00</F65>
			</t>
	</trs>
</day>

Open in new window

AD1080Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
E.g.

DECLARE @XML XML = N'
<day xmlns="x-schema:..\schema_ej.xml" FILE="50716049.002">	
	<trs F1068="SALE" F254="2015-07-16" F253="2015-07-16" F1056="049" F1057="002" F1035="12:35:04" 
		F1036="12:35:47" F1032="6063" F1764="00006036" F1185="20" F1126="498494" F1127="Corrinap" 
		F1148="40101708114395" F1155="LINDA MAHELONA" F1152="2">
			<r F1101="1">
				<itm F01="1000000164" F02="FRESH MEX BURRITO" F04="151" F03="5" F81="1" F1007="7.99" F1006="1" F1080="4.08"/>
				<F65>15.98</F65>
				<F64>2</F64>
				<F1263>0.75</F1263>
				<exc F1511="3401" F1512="Points 1" F1513="TLZ" F1514="1" F1515="16"/>
				<key fn="30"/>
				<key in="0" fn="10011"/>
				<key in="3" fn="720"/>
				<key in="ERROR" fn="30"/>
				<key fn="10011"/>
				<key in="498494" fn="10011"/>
				<key in="8494" fn="10011"/>
				<key in="40101708114395" fn="307"/>
				<key in="2204" fn="710"/>
				<key fn="40"/>
				<key in="2" fn="40"/>
			</r>
			<r F1101="2">
				<itm F01="1000000175" F02="VEGGIE DOG" F04="151" F03="5" F81="1" F1007="4.99" F1006="1" F1080="6.401"/>
				<F65>4.99</F65>
				<F64>1</F64>
				<F1263>0.24</F1263>
				<exc F1511="3401" F1512="Points 1" F1513="TLZ" F1514="1" F1515="5"/>
				<key in="221" fn="710"/>
				<key in="ERROR" fn="30"/>
				<key in="2215" fn="710"/>
			</r>
			<r F1101="3">
				<itm F01="1000035102" F02="SMOOTHIE CREAMY PINA CLDA" F04="21" F03="5" F81="1" F79="1" F1007="9.99" F1006="1" F1080="2.748"/>
				<F65>9.99</F65>
				<F64>1</F64>
				<F1263>0.47</F1263>
				<exc F1511="3401" F1512="Points 1" F1513="TLZ" F1514="1" F1515="10"/>
				<key in="2976" fn="710"/>
			</r>
			<r F1101="4">
				<itm F01="1000058869" F02="POT CHP SALT &amp; VINEGAR" F04="51" F03="9" F81="1" F79="1" F113="STOR" F1007="5.59" F1006="1" F1080="2.079"/>
				<F65>5.59</F65>
				<F64>1</F64>
				<F1263>0.26</F1263>
				<exc F1511="3401" F1512="Points 1" F1513="TLZ" F1514="1" F1515="6"/>
				<key in="856823004028" fn="710"/>
			</r>
			<r F1101="5">
				<fct F1063="131" F02="MasterCard" F113="" F1080="18.496"/>
				<F65>38.27</F65>
				<F64>1</F64>
				<eft now="2015-07-16" typ="CC" acc="************3754=****" ent="S" aut="02813J" ref="004279" bat="20150716" name="RB" term="463733" tcode="0"/>
				<key fn="930"/>
				<key fn="720"/>
				<key fn="190"/>
				<key fn="30"/>
				<key fn="104"/>
			</r>
			<t><tlz F1034="2" F02="TOTAL SALES"/>
				<F65>38.27</F65>
				<F64>1</F64>
			</t>
			<t><tlz F1034="3" F02="Net Sales"/>
				<F65>36.55</F65>
				<F64>5</F64>
			</t>
			<t><tlz F1034="7" F02="Discountable Sales"/>
				<F65>36.55</F65>
				<F64>5</F64>
			</t>
			<t><tlz F1034="21" F02="Taxable 1"/>
				<F65>36.55</F65>
				<F64>5</F64>
			</t>
			<t><tlz F1034="22" F02="Tax 1"/>
				<F65>1.72</F65>
			</t>
			<t><tlz F1034="90" F02="Food stampable"/>
				<F65>15.58</F65>
				<F64>2</F64>
			</t>
			<t><tlz F1034="1231" F02="MasterCard"/>
				<F65>38.27</F65>
				<F64>1</F64>
			</t>
			<t><tlz F1034="3401" F02="Points given"/>
				<F65>0.00</F65>
				<F64>37</F64>
			</t>
			<t><tlz F1034="3415" F02="Admissible spending"/>
				<F65>36.55</F65>
				<F64>5</F64>
			</t>
			<t><tlz F1034="3650" F02="Temporary markdown"/>
				<F65>0.50</F65>
				<F64>1</F64>
			</t>
			<t><tlz F1034="7501" F02="Customer balance current"/>
				<F65>0.00</F65>
			</t>
			<t><tlz F1034="7531" F02="Saving grand total"/>
				<F65>0.50</F65>
				<F64>1</F64>
			</t>
			<t><tlz F1034="7901" F02="Points 1 balance"/>
				<F65>0.00</F65>
				<F64>121</F64>
			</t>
			<t><tlz F1034="29999" F02="BALANCE"/>
				<F65>0.00</F65>
			</t>
	</trs>
</day>';

-- Each 'trs' element has various attributes that could go into an 'ORDER_HEADER' table.
WITH XMLNAMESPACES ( DEFAULT 'x-schema:..\schema_ej.xml' )
	SELECT  trs.value('@F1155', 'NVARCHAR(255)') ,
		trs.value('@F1127', 'NVARCHAR(255)') ,
		trs.value('@F253', 'DATE') ,
		trs.value('@F1148', 'BIGINT') 			
	FROM    @XML.nodes('/day/trs') A ( trs );

-- Each child 'r' element contains line items for each 'trs' record, and could go into an 'ORDER_DETAILS' table.  
WITH XMLNAMESPACES ( DEFAULT 'x-schema:..\schema_ej.xml' )
	SELECT  r.value('../@F1056', 'BIGINT'),
		r.value('../@F1057', 'BIGINT'),
		r.value('../@F1032', 'BIGINT'),
		r.value('@F1101', 'INT'),
		r.value('itm[1]/@F01', 'BIGINT'),
		r.value('itm[1]/@F02', 'NVARCHAR(255)')
	FROM    @XML.nodes('/day/trs/r') A ( r );

-- The 'key' elements would need to into their own table 'KEY_STROKES' using a primary key comprised of  F1056, F1057, F1032 from the 'trs' element, and also F1101 from its parent 'r' element. 
WITH XMLNAMESPACES ( DEFAULT 'x-schema:..\schema_ej.xml' )
	SELECT  [key].value('../../@F1056', 'BIGINT'),
		[key].value('../../@F1057', 'BIGINT'),
		[key].value('../../@F1032', 'BIGINT'),
		[key].value('../@F1101', 'BIGINT'),
		[key].value('@in', 'NVARCHAR(255)'),
		[key].value('@fn', 'INT')
	FROM    @XML.nodes('/day/trs/r/key') A ( [key] );

-- The 't' elements would also need to go into their own table 'TOTALIZERS',  using the following 'trs' elements as its primary key (F1056, F1057, F1032)
WITH XMLNAMESPACES ( DEFAULT 'x-schema:..\schema_ej.xml' )
	SELECT  t.value('../@F1056', 'BIGINT'),
		t.value('../@F1057', 'BIGINT'),
		t.value('../@F1032', 'BIGINT'),
		t.value('@F1101', 'INT'),
		t.value('tlz[1]/@F1034', 'BIGINT'),
		t.value('tlz[1]/@F02', 'NVARCHAR(255)'),
		t.value('F65[1]', 'FLOAT'),
		t.value('F64[1]', 'BIGINT')
	FROM    @XML.nodes('/day/trs/t') A ( t );

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AD1080Author Commented:
Hi,

Thanks very much.  This is an excellent example.  Works perfectly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.