转一个例子:DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()')
EXEC sp_xml_removedocument @idoc
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()')
EXEC sp_xml_removedocument @idoc
DECLARE @doc varchar(1000)
SET @doc ='<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
</ROOT>'EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
select * from AROOT where id in(SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10)))
DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
</ROOT>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement using OPENXML rowset provider.
SELECT * FROM AROOT where ID in (SELECT CustomerID
FROM OPENXML (@idoc, '/ROOT/Customer/Order', 1)
WITH (CustomerID varchar(1000)
))
EXEC sp_xml_removedocument @idoc
create table #tb (doc varchar(8000))
bulk insert #tb from 'c:\ROOT.XML' --XML文件:c:\ROOT.XML--将读取到的数据保存到变量中
set @doc=''
select @doc=@doc+doc from #tb--删除临时表
drop table #tb
go--处理XML文件
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc--执行查询
SELECT * FROM AROOT WHRE ID=
(SELECT * FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(50))
)--释放XML对象
EXEC sp_xml_removedocument @idoc
--要求你的XML文件不能超过8000字节,否则只能改用存储过程处理--从文件中读出XML内容到临时表
create table #tb (doc varchar(8000))
bulk insert #tb from 'c:\ROOT.XML' --XML文件:c:\ROOT.XML--将读取到的数据保存到变量中
set @doc=''
select @doc=@doc+doc from #tb--删除临时表
drop table #tb
go
bulk insert #tb from 'c:\ROOT.XML' --XML文件:c:\ROOT.XML
这样的方法可以实际应用么?
怀疑
问:怎样才能将XML文件导入SQL Server 2000?
答:将XML文件导入SQL Server有若干种方法,这里提供其中的3种:
1.大容量装载COM接口。如果需要将文档的实体和属性析取到关系表中,最快的方法就是使用SQL Server 2000 Extensible Markup Language 3.0 Service Pack 1(SQLXML 3.0 SP1)提供的大容量装载COM接口。大容量状态COM接口包含在SQLXML 3.0 SP1的免费下载中。
2.textcopy.exe命令行实用工具。如果不希望将文档的实体和属性析取到关系表中,您可以使用textcopy.exe命令行实用工具。Textcopy.exe是将 文本和ima3.ge数据类型从单一服务器行或列移入或移出的优秀工具。
数据转换服务(DTS)。如果XML文档很简单,您可以使用DTS将信息逐行析取到表中。这一方法要求您将XML文件定义为输入数据源,将数据库表定义为输 出数据源,并编写ActiveX脚本剖析"<"和">"方式的字符输入,以析取实体、属性及其值。