如何将XML数据导入SQL [英] How can I import XML data into SQL

问题描述

我正尝试使用以下代码读取xml数据:

 声明  @ hdoc   int 

 exec  sp_xml_preparedocument  @ hdoc  输出，
N ' 
<根>
< BookingList>
<预订>
< 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>
<总价格> 162.77</总价格>
< NettPrice> 131.84</NettPrice>
< Commission> 24.42</Commission>
< CommissionVAT> 4.88</CommissionVAT>
<定金> 0.00</定金>
< CardPayment> 0.00</CardPayment>
< CashPayment> 0.00</CashPayment>
< ChequePayment> 0.00</ChequePayment>
< TotalPayment> 0.00</TotalPayment>
<杰出的> 133.47</杰出的>
< BalanceDue> 09-22-11</BalanceDue>
< LastModified> 02-15-12</LastModified>
< SourceCode>代理商预订</SourceCode>
< ProductType> ACC</ProductType>
< ABTA> L8037< ABTA>
< ATOL/>
< LeadPassenger> Watson</LeadPassenger>
< Duration> 3</Duration>
<成人> 2<成人>
< Children> 0lt//Children>
<婴儿> 0</婴儿>
< Seniors> 0</Seniors>
< BookingElements>
< BookingElement>
< BookingItem> 1</BookingItem>
< ElementRef> HBD-71443/1</ElementRef>
< ElementType>酒店</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>
<定金> 0.00</定金>
< LastModified> 09-27-11</LastModified>
< SupplyInEC> Y</SupplyInEC>
< SupplierReference> 53945492</SupplierReference>
<成人> 2<成人>
< Children> 0lt//Children>
<婴儿> 0</婴儿>
< Seniors> 0</Seniors>
< Duration> 3</Duration>
< HotelName> Medplaya Hotel Regente</HotelName>
< Resort>贝尼多姆</Resort>
< Country> ES</Country>
< BoardBasis>半板</BoardBasis>
< StarRating> 3</StarRating>
</BookingElement>
</BookingElements>
< BookingReceipts>
<收据>
<描述>贸易支付</描述>
<金额> 256.06</金额>
< HandlingFee> 0.00</HandlingFee>
< MerchantFee> 0.00</MerchantFee>
</收据>
</BookingReceipts>
< BookingSupplements> </BookingSupplements>
</预订>
</BookingList>
</Root>'

选择 * 来自  openxml ( @ hdoc ，N ' /documents/navreport_hbd_2012-02-15')

我的XML文件是这样开始的:

 <? xml version = "  ?> 
-<   BookingList  > 
-<  预订 > 
<   BookingRef  >  HBD-71443 <  /BookingRef  >  

有人可以帮我解决这个问题吗?

解决方案

http://blog .sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql

http://blogs. microsoft.co.il/blogs/itaybraun/archive/2012/02/23/reading-xml-configuration-files-using-tsql.aspx [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 version="1.0"?>
-<BookingList> 
-<Booking> 
<BookingRef>HBD-71443</BookingRef>

Can anyone help me to sort this out please?

解决方案

http://www.dotnetobject.com/Thread-Reading-XML-File-Using-T-SQL[^]

http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql

http://blogs.microsoft.co.il/blogs/itaybraun/archive/2012/02/23/reading-xml-configuration-files-using-tsql.aspx[^]

这篇关于如何将XML数据导入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！