Click here to Skip to main content
15,917,059 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I am tring to read xml data using the code below:

SQL
declare @hdoc int

exec sp_xml_preparedocument @hdoc output,
N'
<Root>
<BookingList>
<Booking> 
	<BookingRef>HBD-71443</BookingRef> 
	<BookingDate>09-27-11</BookingDate> 
	<DepartureDate>10-22-11</DepartureDate> 
	<ReturnDate>10-25-11</ReturnDate> 
	<DebtorID>0001HAY</DebtorID> 
	<DebtorName>Hays Travel</DebtorName> 
	<BranchID>073</BranchID> 
	<GrossPrice>162.77</GrossPrice> 
	<NettPrice>131.84</NettPrice> 
	<Commission>24.42</Commission> 
	<CommissionVAT>4.88</CommissionVAT> 
	<Deposit>0.00</Deposit> 
	<CardPayment>0.00</CardPayment> 
	<CashPayment>0.00</CashPayment> 
	<ChequePayment>0.00</ChequePayment> 
	<TotalPayment>0.00</TotalPayment> 
	<Outstanding>133.47</Outstanding> 
	<BalanceDue>09-22-11</BalanceDue> 
	<LastModified>02-15-12</LastModified> 
	<SourceCode>Agent Booking</SourceCode> 
	<ProductType>ACC</ProductType> 
	<ABTA>L8037</ABTA> 
	<ATOL/> 
	<LeadPassenger>Watson</LeadPassenger> 
	<Duration>3</Duration> 
	<Adults>2</Adults> 
	<Children>0</Children> 
	<Infants>0</Infants> 
	<Seniors>0</Seniors> 
		<BookingElements> 
			<BookingElement> 
				<BookingItem>1</BookingItem> 
				<ElementRef>HBD-71443/1</ElementRef> 
				<ElementType>Hotel</ElementType> 
				<GrossSale>162.77</GrossSale> 
				<Commission>30.93</Commission> 
				<CommissionVAT>0.00</CommissionVAT> 
				<CreditorID/> <CreditorName/> 
				<CreditorCurrency>EUR</CreditorCurrency> 
				<NettOriginatingCurrency>150.17</NettOriginatingCurrency> 
				<BookingExchangeRate>1.13900</BookingExchangeRate> 
				<NettSterling>131.84</NettSterling> 
				<Deposit>0.00</Deposit> 
				<LastModified>09-27-11</LastModified> 
				<SupplyInEC>Y</SupplyInEC> 
				<SupplierReference>53945492</SupplierReference> 
				<Adults>2</Adults> 
				<Children>0</Children> 
				<Infants>0</Infants> 
				<Seniors>0</Seniors> 
				<Duration>3</Duration> 
				<HotelName>Medplaya Hotel Regente</HotelName> 
				<Resort>Benidorm</Resort> 
				<Country>ES</Country> 
				<BoardBasis>Half Board</BoardBasis> 
				<StarRating>3</StarRating> 
			</BookingElement> 
		</BookingElements> 
		<BookingReceipts> 
			<Receipt> 
				<Description>Trade Payment</Description> 
				<Amount>256.06</Amount> 
				<HandlingFee>0.00</HandlingFee> 
				<MerchantFee>0.00</MerchantFee> 
			</Receipt>
		</BookingReceipts> 
		<BookingSupplements> </BookingSupplements> 
</Booking>
</BookingList>
</Root>'

select * from openxml(@hdoc,N'/documents/navreport_hbd_2012-02-15')


My XML file begins like this:

XML
<?xml version="1.0"?>
-<BookingList> 
-<Booking> 
<BookingRef>HBD-71443</BookingRef>


Can anyone help me to sort this out please?
Posted
Comments
[no name] 29-Mar-12 14:46pm    
what is the error you are facing ?

1 solution

 
Share this answer
 
v2
Comments
milenalukic 30-Mar-12 16:53pm    
Thanks for the links.

What I want to know is how to do this. I created the tables for Booking, Booking Element and Receipt. How do I get the data into all the tables?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900