關於xml方面的介紹可以查看建站學的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'
