OpenXML例子

最新推荐文章于 2022-12-05 14:25:31 发布

Andy__Huang

最新推荐文章于 2022-12-05 14:25:31 发布

阅读量1.1k

点赞数

分类专栏： SqlServer数据库

SqlServer数据库专栏收录该内容

29 篇文章 0 订阅

订阅专栏

DECLARE @XmlDocumentHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<ROOT>
<Customer>
   <CustomerID>VINET</CustomerID>
   <ContactName>Paul Henriot</ContactName>
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer>   
   <CustomerID>LILAS</CustomerID>
   <ContactName>Carlos Gonzlez</ContactName>
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'

EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument

SELECT    *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
     WITH (CustomerID  varchar(10),ContactName varchar(20))
     
SELECT    *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order',1)
     WITH (OrderID int,CustomerID varchar(10), EmployeeID int,OrderDate datetime)

EXEC sp_xml_removedocument @XmlDocumentHandle

--运行结果

/*
CustomerID ContactName
VINET Paul Henriot
LILAS Carlos Gonzlez

OrderID CustomerID EmployeeID OrderDate
10248 VINET 5 1996-07-04 00:00:00.000
10283 LILAS 3 1996-08-16 00:00:00.000
*/