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'