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'

SQL数据库中的XML应用