XML数据导入SQL 2008 [英] XML data import into SQL 2008

问题描述

我需要将一个复杂的XML文件导入到多个SQL表中.我尝试使用数据映射，但一次只能导入1个节点.我需要做的是能够从父节点中选择外键.

有人可以帮忙吗?

这是我的xml的简化版本:

Hi,

I need to import a complex XML file into multiple SQL tables. I have tried using data maps but can only manage to import 1 node at a time. what I need to do is to be able to select foreign keys from parent nodes.

Can someone please help?

Here is a reduced version of my xml:

<BookingList>
	<Booking>
	<BookingRef>HAY-171644</BookingRef>
	<BookingDate>10-15-12</BookingDate>	
		<ContactDetail>
		<Address1>My street name</Address1>		
		</ContactDetail>
		<Passengers>
		<PassengerDetail>
			<PassengerItem>1</PassengerItem>
			<Title>Miss</Title>
			<FirstName>Kerry</FirstName>
		</PassengerDetail>
		</Passengers>
		<BookingElements>
		<BookingElement>
			<BookingItem>1</BookingItem>
			<ElementRef>HAY-171644/1</ElementRef>
			<ElementType>Package</ElementType>
			<AccommodationDetail>
				<Accommodation>
					<AccommodationItem>1</AccommodationItem>
					<CheckIn>10-21-12</CheckIn>
					<CheckOut>10-28-12</CheckOut>
					<Rooms>
						<RoomItem>1</RoomItem>
						<RoomType> Double room</RoomType>
					</Rooms>
				</Accommodation>
			</AccommodationDetail>
		</BookingElement>
		</BookingElements>
	</Booking>
	<Booking>
	<BookingRef>HAY-171643</BookingRef>
	<BookingDate>10-15-12</BookingDate>
		<ContactDetail>
		<Address1>30 Street name</Address1>
		</ContactDetail>
		<Passengers>
		<PassengerDetail>
			<PassengerItem>1</PassengerItem>
			<Title>Mr</Title>
			<FirstName>ALWYN</FirstName>
		</PassengerDetail>
		</Passengers>
		<BookingElements>
		<BookingElement>
			<BookingItem>1</BookingItem>
			<ElementRef>HAY-171643/1</ElementRef>
			<ElementType>Package</ElementType>
			<AccommodationDetail>
				<Accommodation>
					<AccommodationItem>1</AccommodationItem>
					<CheckIn>06-19-13</CheckIn>
					<CheckOut>06-26-13</CheckOut>
						<Rooms>
							<RoomItem>1</RoomItem>
							<RoomType>Studio for 4</RoomType>
						</Rooms>
				</Accommodation>
			</AccommodationDetail>
		</BookingElement>
		</BookingElements>
	</Booking>
</BookingList>

推荐答案

我不知道确切的答案，但我希望它能帮助您找到解决方案:

I don''t know the exact answer, but i hope it will help you to find a solution:

DECLARE @iDoc INT
DECLARE @xml as XML 

SET @xml = N'<bookinglist>...</bookinglist>'

--Booking
EXEC sp_xml_preparedocument @iDoc OUTPUT, @xml
SELECT *
FROM OPENXML(@iDoc,'/BookingList/Booking',2)
WITH ([BookingRef] NVARCHAR(50)  'BookingRef',
	[BookingDate] NVARCHAR(20)  'BookingDate')
EXEC sp_xml_removedocument @iDoc

--ContactDetail
EXEC sp_xml_preparedocument @iDoc OUTPUT, @xml
SELECT *
FROM OPENXML(@iDoc,'/BookingList/Booking/ContactDetail',2)
WITH ([Address] NVARCHAR(50)  'Address1')
EXEC sp_xml_removedocument @iDoc

--Passengers
EXEC sp_xml_preparedocument @iDoc OUTPUT, @xml
SELECT *
FROM OPENXML(@iDoc,'/BookingList/Booking/Passengers/PassengerDetail',2)
WITH ([PassengerItem] INT  'PassengerItem',
	[Title] NVARCHAR(20)  'Title',
	[FirstName] NVARCHAR(20)  'FirstName')
EXEC sp_xml_removedocument @iDoc

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