1、首先要将SQL Server 的xp_cmdshell 的功能启动,随后我们将使用xp_cmdshell 来读取XML文档内容,执行如下脚本
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;
2、请将以下的XML内容保存成aa.xml,并存于“C:\”下。
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table>
<productID>1</productID>
<productCode>04ZS1-1</productCode>
<productName>单人沙发</productName>
<providerID>1</providerID>
<productTypeID>11</productTypeID>
<productKindID>12</productKindID>
<productColor>0</productColor>
<productBuyPrice>994.0000</productBuyPrice>
<productWholesalePrice>994.0000</productWholesalePrice>
<productSalePrice>2237.00</productSalePrice>
<productVolume>0</productVolume>
<productIsUse>0</productIsUse>
<productType>0</productType>
<IsMaterial>1</IsMaterial>
<saleQuotiety>3</saleQuotiety>
<isSplit>1</isSplit>
<isUpdate>0</isUpdate>
<syncid>6745</syncid>
<SetMoney>1518.00</SetMoney>
<IsQm>1</IsQm>
<materialGradeID>0</materialGradeID>
<IsZDY>0</IsZDY>
<UpdateTime>2009-06-12T14:06:02.467+08:00</UpdateTime>
<periodType>0</periodType>
</Table>
<Table>
<productID>2</productID>
<productCode>04ZS1-1</productCode>
<productName>单人沙发</productName>
<providerID>1</providerID>
<productTypeID>11</productTypeID>
<productKindID>12</productKindID>
<productIntCode>10367</productIntCode>
<productUnit>箱</productUnit>
<productColor>0</productColor>
<productBuyPrice>994.0000</productBuyPrice>
<productWholesalePrice>994.0000</productWholesalePrice>
<productSalePrice>2237.00</productSalePrice>
<productVolume>0.48</productVolume>
<productWeight>36</productWeight>
<productIsUse>0</productIsUse>
<productType>1</productType>
<IsMaterial>1</IsMaterial>
<saleQuotiety>2.25</saleQuotiety>
<isSplit>1</isSplit>
<isUpdate>0</isUpdate>
<syncid>6746</syncid>
<SetMoney>1656.00</SetMoney>
<IsQm>1</IsQm>
<materialGradeID>0</materialGradeID>
<IsZDY>0</IsZDY>
<periodType>0</periodType>
</Table>
</NewDataSet>
<NewDataSet>
<Table>
<productID>1</productID>
<productCode>04ZS1-1</productCode>
<productName>单人沙发</productName>
<providerID>1</providerID>
<productTypeID>11</productTypeID>
<productKindID>12</productKindID>
<productColor>0</productColor>
<productBuyPrice>994.0000</productBuyPrice>
<productWholesalePrice>994.0000</productWholesalePrice>
<productSalePrice>2237.00</productSalePrice>
<productVolume>0</productVolume>
<productIsUse>0</productIsUse>
<productType>0</productType>
<IsMaterial>1</IsMaterial>
<saleQuotiety>3</saleQuotiety>
<isSplit>1</isSplit>
<isUpdate>0</isUpdate>
<syncid>6745</syncid>
<SetMoney>1518.00</SetMoney>
<IsQm>1</IsQm>
<materialGradeID>0</materialGradeID>
<IsZDY>0</IsZDY>
<UpdateTime>2009-06-12T14:06:02.467+08:00</UpdateTime>
<periodType>0</periodType>
</Table>
<Table>
<productID>2</productID>
<productCode>04ZS1-1</productCode>
<productName>单人沙发</productName>
<providerID>1</providerID>
<productTypeID>11</productTypeID>
<productKindID>12</productKindID>
<productIntCode>10367</productIntCode>
<productUnit>箱</productUnit>
<productColor>0</productColor>
<productBuyPrice>994.0000</productBuyPrice>
<productWholesalePrice>994.0000</productWholesalePrice>
<productSalePrice>2237.00</productSalePrice>
<productVolume>0.48</productVolume>
<productWeight>36</productWeight>
<productIsUse>0</productIsUse>
<productType>1</productType>
<IsMaterial>1</IsMaterial>
<saleQuotiety>2.25</saleQuotiety>
<isSplit>1</isSplit>
<isUpdate>0</isUpdate>
<syncid>6746</syncid>
<SetMoney>1656.00</SetMoney>
<IsQm>1</IsQm>
<materialGradeID>0</materialGradeID>
<IsZDY>0</IsZDY>
<periodType>0</periodType>
</Table>
</NewDataSet>
3、执行以下命令
SELECT * into #temp1 FROM OPENROWSET(
BULK 'C:\aa.xml' ,SINGLE_BLOB ) AS x
DECLARE @hdoc int
DECLARE @doc xml
select @doc=BulkColumn from #temp1
EXEC sp_xml_preparedocument @hdoc OUTPUT,@doc
SELECT * into #temp2
FROM OPENXML (@hdoc, '/NewDataSet/Table',3)
with (productID int,
productCode varchar(50),
productName varchar(50) )
exec sp_xml_removedocument @hdoc
select * from #temp1
select * from #temp2
drop table #temp1
drop table #temp2