Shredding with XQuery to Parent, Child, Grandchild tables

Question

Shredding with XQuery to Parent, Child, Grandchild tables

wdjong

SSC Enthusiast

Points: 137
More actions
April 4, 2012 at 7:01 pm

#100020

Hi,
I'm sorry if this has been addressed already. I haven't been able to find it.
Please consider the following XML.
I need help to get to the next level.
Each Purchase Order record can relate to 0..n Workorder records which can contain 0..n items.
I've shown my 3rd table query but it generates duplicates...
Thanks
Walter
DECLARE @xml XML = '<PurchaseOrder>
<PurchaseOrderNumber>1</PurchaseOrderNumber>
<WorkOrder>
<WorkOrderNumber>11</WorkOrderNumber>
<Item>
<ItemNumber>111</ItemNumber>
</Item>
<Item>
<ItemNumber>112</ItemNumber>
</Item>
</WorkOrder>
<WorkOrder>
<WorkOrderNumber>12</WorkOrderNumber>
<Item>
<ItemNumber>121</ItemNumber>
</Item>
<Item>
<ItemNumber>122</ItemNumber>
</Item>
</WorkOrder>
</PurchaseOrder>'
SELECT P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumber
FROM @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P)
SELECT W.value('WorkOrderNumber[1]', 'VARCHAR(20)') WorkOrderNumber,
P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumber
FROM @xml.nodes('/PurchaseOrder/WorkOrder') AS WorkOrder(W)
CROSS APPLY @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P)
-- this doesn't work: All the items have 2 records (1 for each workorder)
SELECT I.value('ItemNumber[1]', 'VARCHAR(20)') ItemNumber,
W.value('WorkOrderNumber[1]', 'VARCHAR(20)') WorkOrderNumber,
P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumber
FROM @xml.nodes('/PurchaseOrder/WorkOrder/Item') AS Item(I)
CROSS APPLY @xml.nodes('/PurchaseOrder/WorkOrder') AS WorkOrder(W)
CROSS APPLY @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Arthur Olcot SSCertifiable Points: 6651 More actions · Answer 1

wdjong (4/4/2012)
Hi,
I'm sorry if this has been addressed already. I haven't been able to find it.
Please consider the following XML.
I need help to get to the next level.
Each Purchase Order record can relate to 0..n Workorder records which can contain 0..n items.
I've shown my 3rd table query but it generates duplicates...
Thanks
Walter
DECLARE @xml XML = '<PurchaseOrder>
<PurchaseOrderNumber>1</PurchaseOrderNumber>
<WorkOrder>
<WorkOrderNumber>11</WorkOrderNumber>
<Item>
<ItemNumber>111</ItemNumber>
</Item>
<Item>
<ItemNumber>112</ItemNumber>
</Item>
</WorkOrder>
<WorkOrder>
<WorkOrderNumber>12</WorkOrderNumber>
<Item>
<ItemNumber>121</ItemNumber>
</Item>
<Item>
<ItemNumber>122</ItemNumber>
</Item>
</WorkOrder>
</PurchaseOrder>'
SELECT P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumber
FROM @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P)
SELECT W.value('WorkOrderNumber[1]', 'VARCHAR(20)') WorkOrderNumber,
P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumber
FROM @xml.nodes('/PurchaseOrder/WorkOrder') AS WorkOrder(W)
CROSS APPLY @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P)
-- this doesn't work: All the items have 2 records (1 for each workorder)
SELECT I.value('ItemNumber[1]', 'VARCHAR(20)') ItemNumber,
W.value('WorkOrderNumber[1]', 'VARCHAR(20)') WorkOrderNumber,
P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumber
FROM @xml.nodes('/PurchaseOrder/WorkOrder/Item') AS Item(I)
CROSS APPLY @xml.nodes('/PurchaseOrder/WorkOrder') AS WorkOrder(W)
CROSS APPLY @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P)

Hi,

I think I understand what you are looking to achieve and you were very close. I have reworked your third query to the following but just changing the cross apply statements:

SELECT I.value('ItemNumber[1]', 'VARCHAR(20)') ItemNumber,

W.value('WorkOrderNumber[1]', 'VARCHAR(20)') WorkOrderNumber,

P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumber

FROM @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P)

CROSS APPLY p.nodes('WorkOrder') AS WorkOrder(W)

CROSS APPLY w.nodes('Item') AS Item(I)

wdjong SSC Enthusiast Points: 137 More actions · Answer 2

Thanks! I just ran the output and it looks perfect. I really appreciate your taking the time to share you understanding. (maybe you'll be pleased to know that I work for a not-for-profit / charity org. so it was in a good cause). Regards Walter

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

I guess that I'm still amazed that such a simple structure requires 3 selects to return the hierarchy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76996 More actions · Answer 4

Jeff Moden (4/5/2012)
I guess that I'm still amazed that such a simple structure requires 3 selects to return the hierarchy.

It doesn't. As with almost everything, there are multiple ways to approach a task. The solution provided above used a top-down approach, but the bottom-up approach is much simpler.

SELECT I.value('./ItemNumber[1]', 'VARCHAR(20)') AS ItemNumber

,I.value('../WorkOrderNumber[1]', 'VARCHAR(20)') AS WorkOrderNumber

,I.value('../../PurchaseOrderNumber[1]', 'VARCHAR(20)') AS PurchaseOrderNumber

FROM @xml.nodes('/PurchaseOrder/WorkOrder/Item') AS Item(I)

I haven't tested to see which is more efficient.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

LutzM SSC Guru Points: 107049 More actions · Answer 5

The bottom-up might be simpler. But internally, it'll "climb up" one and two nodes for each "bottom" element. Depending on the xml size and structure this approach is less efficient (at least most of the time).

The top-bottom approach is more like shredding each node level separately and then join those shredded result sets using internal pointers. (At least that's my understanding how the CROSS APPLY works against a xml node).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Chris W Johnson SSC Rookie Points: 37 More actions · Answer 6

Chris W Johnson

SSC Rookie

Points: 37

April 18, 2012 at 4:13 pm

#1475772

Thanks for this. It helped me immensely.

Chris