How to Apply An XML in SQL Database

In this paper, we will introduce a function of the joint Merge train in SQL SERVER2005 by using an XML technology.

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> Works odd numbers </ SoNo>
<ItemID> Product ID </ ItemID>
<EditionCD> Version CD </ EditionCD>
<ImprintCD> Press CD </ ImprintCD>
<ProductName> Product Name </ ProductName>
<EditionName> Version </ EditionName>
<ImprintName> Press </ ImprintName>
<PackType> Bag Type </ PackType>
<ExFactory> First take delivery </ ExFactory>
<OrderQty> Orders </ OrderQty>
<PlanProductQty> Small number of installments scheduled row </ 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))

—- Merge train

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 ‘

Recommended Download