关于XML在SQL数据库中的应用
作者：[网上资料 ]
关于xml方面的介绍可以查看建站学的xml教程栏目，本文我们将介绍一种通过XML技术在SQLSERVER2005中进行合合并专列的功能
DECLARE @idoc int DECLARE @doc xml
SET @doc =' <condition>
<State csif_cStateID="S001"/> <State csif_cStateID="S002"/> <State csif_cStateID="S003"/>
</condition> '
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT csif_cStateID FROM OPENXML (@idoc, '/condition/State') WITH (csif_cStateID varchar(15)) EXEC sp_xml_removedocument @idoc
DECLARE @docHandle int declare @xmlDocument nvarchar(max) -- or xml type set @xmlDocument = N'
<Item> <SoNo>工程单号</SoNo> <ItemID>产品ID</ItemID> <EditionCD>版本CD</EditionCD> <ImprintCD>出版社CD</ImprintCD> <ProductName>产品名称</ProductName> <EditionName>版本</EditionName> <ImprintName>出版社</ImprintName> <PackType>袋型</PackType> <ExFactory>最早走货期</ExFactory> <OrderQty>订单数量</OrderQty> <PlanProductQty>小排期排期数</PlanProductQty> </Item>
' EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument -- Use OPENXML to provide rowset consisting of customer data. --INSERT Customers --SELECT * --FROM OPENXML(@docHandle, N'ROOT/Customers/Orders') -- WITH Customers -- Use OPENXML to provide rowset consisting of order data. --INSERT Orders --SELECT * --FROM OPENXML(@docHandle, N'//Orders') -- WITH Orders -- Using OPENXML in a SELECT statement. SELECT * FROM OPENXML(@docHandle, N'/Item',2) WITH (SoNo nchar(20) , ItemID nvarchar(10),EditionCD nvarchar(10),ImprintCD nvarchar(10)) EXEC sp_xml_removedocument @docHandle --EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- --SELECT reqno --FROM OPENXML (@idoc, '/root/CourierID',1) --WITH (Reqno varchar(15))
----合并专列
from (select distinct rcpi_cCartonContentID,rcpi_iCartonProductID from #tempOldRcpi ) a Outer apply ( select stuff( replace( replace ( (select distinct isnull(so,'''') as so from #tempOldRcpi b where b.rcpi_iCartonProductID=a.rcpi_iCartonProductID for xml auto),''<b so="'',''\''),''"/>'',''''),1,1,'''') as So
) as c'
|
DECLARE @idoc int DECLARE @doc xml
SET @doc =' <condition>
<State csif_cStateID="S001"/> <State csif_cStateID="S002"/> <State csif_cStateID="S003"/>
</condition> '
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT csif_cStateID FROM OPENXML (@idoc, '/condition/State') WITH (csif_cStateID varchar(15)) EXEC sp_xml_removedocument @idoc
DECLARE @docHandle int declare @xmlDocument nvarchar(max) -- or xml type set @xmlDocument = N'
<Item> <SoNo>工程单号</SoNo> <ItemID>产品ID</ItemID> <EditionCD>版本CD</EditionCD> <ImprintCD>出版社CD</ImprintCD> <ProductName>产品名称</ProductName> <EditionName>版本</EditionName> <ImprintName>出版社</ImprintName> <PackType>袋型</PackType> <ExFactory>最早走货期</ExFactory> <OrderQty>订单数量</OrderQty> <PlanProductQty>小排期排期数</PlanProductQty> </Item>
' EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument -- Use OPENXML to provide rowset consisting of customer data. --INSERT Customers --SELECT * --FROM OPENXML(@docHandle, N'ROOT/Customers/Orders') -- WITH Customers -- Use OPENXML to provide rowset consisting of order data. --INSERT Orders --SELECT * --FROM OPENXML(@docHandle, N'//Orders') -- WITH Orders -- Using OPENXML in a SELECT statement. SELECT * FROM OPENXML(@docHandle, N'/Item',2) WITH (SoNo nchar(20) , ItemID nvarchar(10),EditionCD nvarchar(10),ImprintCD nvarchar(10)) EXEC sp_xml_removedocument @docHandle --EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- --SELECT reqno --FROM OPENXML (@idoc, '/root/CourierID',1) --WITH (Reqno varchar(15))
----合并专列
from (select distinct rcpi_cCartonContentID,rcpi_iCartonProductID from #tempOldRcpi ) a Outer apply ( select stuff( replace( replace ( (select distinct isnull(so,'''') as so from #tempOldRcpi b where b.rcpi_iCartonProductID=a.rcpi_iCartonProductID for xml auto),''<b so="'',''\''),''"/>'',''''),1,1,'''') as So
) as c'
|