SQL Server Xquery retreive generated id for child parent relationship

Question

Post reply

SQL Server Xquery retreive generated id for child parent relationship

LeeLuv

Mr or Mrs. 500

Points: 588
More actions
February 12, 2014 at 3:47 am

#282274

Is it possible to use Xpath in a query to get child parent relation ship using XQuery. If I use EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<xmlns:ns="http://some_namespace"/>' and after that use the metadata properties I will receive a correct parent - child id relationship i.e

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LeeLuv Mr or Mrs. 500 Points: 588 More actions · Answer 1

sorry for got to add my sample xml

PurchaseOrder BuyerName=“Car Corporation” Date=“1 Jan 2000”>

</ItemsBought>

</Payments>

</PurchaseOrder>

Hunchback SSCrazy Points: 2649 More actions · Answer 2

Can you post the expected result?

It is not clear if there is a relashionship between ites and payments.

SET NOCOUNT ON;

USE tempdb;

GO

DECLARE @x xml = N'<PurchaseOrder ID="1" BuyerName="Car Corporation" Date="1 Jan 2000">

<ItemsBought>

<Item PartId="1" Cost="3000"/>

<Item PartId="2" Cost="6000"/>

</ItemsBought>

<Payments>

<Payment CreditCard="8342398432" ChargeAmt="8000.00"/>

<Payment CreditCard="3474324934" ChargeAmt="2000.00"/>

</Payments>

</PurchaseOrder>';

-- items

SELECT

n1.x.value('@ID[1]', 'int') PO_ID,

n1.x.value('@BuyerName[1]', 'varchar(35)') BuyerName,

n1.x.value('@Date[1]', 'date') PO_Date,

n2.x.value('@PartId[1]', 'int') AS PartId,

n2.x.value('@Cost[1]', 'money') AS Cost

FROM

@x.nodes('PurchaseOrder') AS n1(x)

CROSS APPLY

n1.x.nodes('ItemsBought/Item') AS n2(x);

-- payments

SELECT

n1.x.value('@ID[1]', 'int') PO_ID,

n1.x.value('@BuyerName[1]', 'varchar(35)') BuyerName,

n1.x.value('@Date[1]', 'date') PO_Date,

n2.x.value('@CreditCard[1]', 'varchar(16)') AS CreditCard,

n2.x.value('@ChargeAmt[1]', 'money') AS ChargeAmt

FROM

@x.nodes('PurchaseOrder') AS n1(x)

CROSS APPLY

n1.x.nodes('Payments/Payment') AS n2(x);

GO

LeeLuv Mr or Mrs. 500 Points: 588 More actions · Answer 3

thanx for the help

the out put need to be like this :

PurchaseOrder Table

Id BuyerName Date

50 CarCorporation 1 Jan2000

Item Table

IdParentIdOrderPartIdCost

20 50113000

2150216000

Payment Table

IdParentIdOrderCreditCard ChargeAmt

30 50183423984328000

3151234743249342000

Hunchback SSCrazy Points: 2649 More actions · Answer 4

In your sample document there was no "ID" for purchase orders so I added to make it easier.

What elements are you referencing for "ParentID"?

PO (50)?

Items (20, 21)?

Payments (30, 31)?

ParentId (50, 51)?