SQL数据库中的XML应用
下面我介绍一种通过XML技术在SQL SERVER2005中进行合併行轉列的 功能
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'
作者： Leo_wl
出处： http://www.cnblogs.com/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息