SQL数据库中的XML应用

下面我介绍一种通过XML技术在SQL SERVER2005中进行合併行轉列的功能

DECLARE @idoc int
DECLARE @doc xml

SET @doc ='
<condition>

</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'