For fun:-D, here is a full shredding code

😎

DECLARE @XORDER XML = N'<?xml version="1.0" encoding="utf-16"?>

<order>

<Product Index="e1e2c499-f9...etc...." ProductID="12">

<Attribute Name="Paper...etc..." />

<Attribute Name="Ink..etc.." />

<Attribute Name="Bleed ..etc " />

<Drops returnCompany="ACME" returnAddress1="...etc...">

<Drop Number="1" Total="3141" Date="07/06/2014">

<Area Name="43001PBOX" FriendlyName="ALEXANDRIA, OH" Total="165" />

<Area Name="43001R001" FriendlyName="ALEXANDRIA, OH" Total="393" />

<Area Name="43001R002" FriendlyName="ALEXANDRIA, OH" Total="445" />

<Area Name="43023PBOX" FriendlyName="GRANVILLE, OH" Total="236" />

<Area Name="43023R001" FriendlyName="GRANVILLE, OH" Total="666" />

<Area Name="43023R002" FriendlyName="GRANVILLE, OH" Total="497" />

<Area Name="43031PBOX" FriendlyName="JOHNSTOWN, OH" Total="166" />

<Area Name="43031R001" FriendlyName="JOHNSTOWN, OH" Total="573" />

</Drop>

<Drop Number="2" Total="4279" Date="07/27/2014">

<Area Name="43031R002" FriendlyName="JOHNSTOWN, OH" Total="611" />

<Area Name="43031R003" FriendlyName="JOHNSTOWN, OH" Total="582" />

<Area Name="43031R004" FriendlyName="JOHNSTOWN, OH" Total="533" />

<Area Name="43031R005" FriendlyName="JOHNSTOWN, OH" Total="511" />

<Area Name="43055R003" FriendlyName="NEWARK, OH" Total="638" />

<Area Name="43062PBOX" FriendlyName="PATASKALA, OH" Total="361" />

<Area Name="43062R009" FriendlyName="PATASKALA, OH" Total="516" />

<Area Name="43062R017" FriendlyName="PATASKALA, OH" Total="527" />

</Drop>

</Drops>

</Product>

</order>';

DECLARE @AREA_NAME VARCHAR(10) = '43031PBOX';

SELECT

PRO.DUCT.value('@ProductID','INT') AS ProductID

,DRO.PS.value('@returnCompany','VARCHAR(50)') AS returnCompany

,DR.OP.value('@Number','INT') AS Number

,DR.OP.value('@Total','INT') AS Total

,DR.OP.value('@Date','DATE') AS Date

,AR.EA.value('@Name','VARCHAR(10)') AS Name

,AR.EA.value('@FriendlyName','VARCHAR(50)') AS FriendlyName

,AR.EA.value('@Total','INT') AS Total

FROM @XORDER.nodes('order/Product') AS PRO(DUCT)

OUTER APPLY PRO.DUCT.nodes('Drops') AS DRO(PS)

OUTER APPLY DRO.PS.nodes('Drop') AS DR(OP)

OUTER APPLY DR.OP.nodes('Area') AS AR(EA)

WHERE AR.EA.value('@Name','VARCHAR(10)') = @AREA_NAME

Results

ProductID returnCompany Number Total Date Name FriendlyName Total

----------- -------------- ------- ------ ---------- ---------- ---------------- ------

12 ACME 1 3141 2014-07-06 43031PBOX JOHNSTOWN, OH 166