OpenXML例子

来源：互联网发布：美国海军知乎编辑：程序博客网时间：2024/05/18 01:08

DECLARE @XmlDocumentHandle intDECLARE @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, @XmlDocumentSELECT    *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
*/