Little help need with a WHERE clause

  • 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?

  • 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.

  • Nice! Thanks!

  • 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