Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Little help need with a WHERE clause Expand / Collapse
Author
Message
Posted Monday, June 9, 2014 6:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
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?


Post #1578791
Posted Monday, June 9, 2014 7:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:00 PM
Points: 341, Visits: 751
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.

Post #1578802
Posted Monday, June 9, 2014 7:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
Nice! Thanks!
Post #1578806
Posted Saturday, June 21, 2014 8:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 2,533, Visits: 7,099
For fun, 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
Post #1584602
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse