June 9, 2014 at 6:59 am
Hello all.
I'm pretty new to querying against XML data. I've been learning on the fly here.
I have a XML doc that looks like this:
<?xml version="1.0" encoding="utf-16"?>
<order>
<Product Index="e1e2c499-f9...etc...." ProductID="12" ..etc..>
<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>
I can successfully, by trial an error, grab all the 'Area Names' which this query:
DECLARE @myXmlData xml
SET @myXmlData = (SELECT OrderItemXml FROM OrderItems WHERE OrderItemID = @orderItemID)
SELECT T.C.value('@Name', 'varchar(50)') FROM @myXMLData.nodes('Product/Drops/Drop/Area') T(C)
This brings me all "Area Names" - all 16 of them. But what I'd REALLY like to do is query out Area Names per "Drop". So, somehow, I'd like to add a WHERE clause to only grab those from Drop Number 1 or which ever drop I specify.
Any pointers? Any help?
June 9, 2014 at 7:31 am
This might work:
SELECT T.C.value('@Name', 'varchar(50)') FROM @myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') T(C)
note the test in brackets for the attribute Number.
June 9, 2014 at 7:37 am
Nice! Thanks!
June 21, 2014 at 8:56 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply