Link to home
Start Free TrialLog in
Avatar of AD1080
AD1080

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AD1080
AD1080

ASKER

Hi,

Thanks very much.  This is an excellent example.  Works perfectly.