8 Replies Latest reply: Jul 18, 2013 3:48 PM by PK_UpNorth RSS

    Help - Nested XMLQuery (XQuery) Syntax

    PK_UpNorth

      I need to query a relational table that includes an XMLType column as Binary XML.  The XMLType column contains an XML document of arbitrary length. My query is in part dependent on relational column values and data values within the XML documents.  The body of the XML document, beneath the root element, is composed of 3 main sections (elements): Header1, 1/document; Header2, 1/document; and Details, 1/document.  The Details section is composed of multiple, arbitrary in number, Detail sections (elements). Example data and table layout are below.

       

      For a particular TLID or CUST, or SHIP_DATE, etc. I need to return the Header1, Header2, and multiple Detail sections where element values within particular Detail sections match additional qualifications.

       

      If I just want to return the Detail sections, I can do this successfully with the following query:

       

      select cust,  tlid,  xmlquery('declare default element namespace "http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd"; (::)

                              for $i in /C52R09/DETAILS/DETAIL

                              where $i/OrderNumber = "SBC00999"

                              return $i' PASSING xml_doc

                              RETURNING CONTENT).getClobVal() detail

      from xml_truck_info

      where tlid = '424500'

          and  xmlexists('declare default element namespace "http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd"; (::)

                       $x/C52R09/HEADER1[TruckNumber = "424500"]' PASSING xml_doc AS "x");

                     

      I need, however, to return the Header1 and Header2 sections once per document if the query returns any Detail sections, regardless of  whether the query returns 1 or multiple Detail sections.

      My impression is that this will require a nested XQuery.  I haven't had success getting the syntax correct for this.  I've wasted a tremendous amount of time searching the web for examples that would replicate my scenario.  It seems there is a dearth of detailed, clear info and examples available on Oracle's XQuery implementation and structure.  I found a few examples that approximate what I'm trying to do; however, when I tweak them, the query spits up.

       

      Based on the following 2 examples pulled from the web or Oracle documentation:

       

      SELECT rownum, XMLQuery(

         <counties>

          {for $c in ora:view("CHAMBER_OF_COMMERCE")

           let $coc_county := $c/ROW/COC_COUNTY/text(),

               $coc_name := $c/ROW/COC_NAME,

               $coc_phone := $c/ROW/COC_PHONE/textb()

           where $coc_county = $cc_county/county/text()

           order by $coc_county

           return

              <county population="{xs:unsignedInt(sum(/cities/city/population))}">

                 <name>{$coc_county}</name>

                 <chamber phone="{$coc_phone}">{$coc_name/text()}</chamber>

                 <attractions>

                    {for $a in collection("/public")

                     where $coc_county = $a/attraction/county/text()

                     return $a

                    }

                 </attractions>

              </county>}

          </counties>

          PASSING BY VALUE cc_city_populations,

             XMLTYPE('<county>' || cc_county || '</county>') AS "cc_county"

          RETURNING CONTENT)

      FROM county_census;

       

      and

       

      SELECT XMLQuery(

      'for $i in $h//channel

      return

      <headlines>

      <title>OTN new articles on Oracle Solaris</title>

      <items>

         {

           for $j in $h//item

           where ora:contains($j, "Oracle Solaris")

           return <item> {($j/title, $j/link)}</item>

         }

      </items>

      </headlines>'

      PASSING xmlparse (document httpuritype ('http://feeds.delicious.com/v2/rss/OracleTechnologyNetwork/

      otntecharticle').getCLOB()) as "h"

      RETURNING CONTENT).getStringVal() as RESULT FROM DUAL;

       

      I have modified my simple, successful query above to the following:

       

      select tlid, XMLQuery('declare default element namespace "http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd"; (::)

                    for $i in /C52R09

                    return

                      <Headers>

                        "{$i/Header1}"

                        "{$i/Header2}"

                      </Headers>

                      {

                          for $x in $i/DETAILS/DETAIL

                          where $x/MarvinOrderNumber = "SBC00999"

                          return $x

                      }'

                      PASSING xml_doc

                    RETURNING CONTENT).getClobVal() detail

      from xml_truck_info

      where TLID = '424500'

          and  xmlexists('declare default element namespace "http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd"; (::)

                       $x/C52R09/HEADER1[TruckNumber = "424500"]' PASSING xml_doc AS "x");

       

      When run in SQLDeveloper the result consistently reflects an issue with the curly braces '{}' around the second 'for' expression.  I have run this with different iterations, to include removing the curly braces around the $i/Header1 and $i/Header2 sections.  It makes no difference.  I get similar results as follows:

       

      Error at Command Line:16 Column:6

      Error report:

      SQL Error: ORA-19114: XPST0003 - error during parsing the XQuery expression:

      LPX-00801: XQuery syntax error at '{'

      9                   {

      -                   ^

      19114. 00000 -  "error during parsing the XQuery expression: %s"

      *Cause:    An error occurred during the parsing of the XQuery expression.

      *Action:   Check the detailed error message for the possible causes.

       

       

      My table, XML_TRUCK_INFO, looks like this:

       

      Name                Type

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

      CUST                VARCHAR2(7)

      LOC                 VARCHAR2(5)

      TLID                NUMBER

      STID                NUMBER

      SHIP_DATE           DATE

      SHIPPED_FLAG        VARCHAR2(1)

      XML_DOC             SYS.XMLTYPE STORAGE BINARY    

       

      Here is a sample of the XML_DOC content that I am running  the XQuery against.

       

      <?xml version="1.0" standalone="yes"?>

      <C52R09 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

           xsi:noNamspaceSchemaLocation="http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd                                           

              http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd">

        <HEADER1>

          <TruckNumber>424500</TruckNumber>

          <StopID>16</StopID>

          <ShipFrom>CITYNAME</ShipFrom>

          <ShippingDate>10-JUN-2013</ShippingDate>

          <PlannedDepartureDate>10-JUN-2013 05:00</PlannedDepartureDate>

          <ETADate>11-JUN-2013</ETADate>

          <LoadName>SW 29</LoadName>

          <StopName>16-FREEPORT, </StopName>

          <StopComment/>

          <TruckStatus>INVOICED</TruckStatus>

          <ReportType>C</ReportType>

        </HEADER1>

        <HEADER2>

          <DestAddrName>CUSTOMER-BUSINESS-NAME</DestAddrName>

          <DestAddrLine1>23 EAST 4TH AVENUE</DestAddrLine1>

          <DestAddrLine2/>

          <DestCity/>

          <DestState/>

          <DestZip>55555</DestZip>

        </HEADER2>

        <DETAILS>

          <DETAIL>

            <OrderNumber>SBC00999</OrderNumber>

            <LineNumber>3</LineNumber>

            <OrderType>STANDARD SALES ORDER</OrderType>

            <OrderStatus>SHIPPED COMPLETE</OrderStatus>

            <OrderDate>23-MAY-2013</OrderDate>

            <JobName>Job1</JobName>

            <QtyOrdered>3</QtyOrdered>

            <QtyShipped>3</QtyShipped>

            <WeekofDelivery>10-JUN-2013</WeekofDelivery>

            <Status>THIS TRUCK</Status>

            <CustomerNumber>5000-000</CustomerNumber>

            <CustomerName>CUSTNAME1</CustomerName>

            <CustomerPONumber>W163409</CustomerPONumber>

            <CubicFeet>4.56</CubicFeet>

            <ListPrice>677</ListPrice>

            <SpecialMQSCode>

              <ProductType>AAZG</ProductType>

              <UnitType>ABEG</UnitType>

            </SpecialMQSCode>

            <ShortDescription>INSERT ASSEMBLY</ShortDescription>

            <OpeningCount>5</OpeningCount>

            <TrackingLines>

              <TrackingNo>0YD746</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:05</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

              <TrackingNo>0YD747</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:31</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

              <TrackingNo>0YD748</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:06</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

            </TrackingLines>

          </DETAIL>

          <DETAIL>

            <OrderNumber>SBC00999</OrderNumber>

            <LineNumber>4</LineNumber>

            <OrderType>STANDARD SALES ORDER</OrderType>

            <OrderStatus>SHIPPED COMPLETE</OrderStatus>

            <OrderDate>23-MAY-2013</OrderDate>

            <JobName>Job1</JobName>

            <QtyOrdered>3</QtyOrdered>

            <QtyShipped>3</QtyShipped>

            <WeekofDelivery>10-JUN-2013</WeekofDelivery>

            <Status>THIS TRUCK</Status>

            <CustomerNumber>5000-000</CustomerNumber>

            <CustomerName>CUSTNAME1</CustomerName>

            <CustomerPONumber>W163409</CustomerPONumber>

            <CubicFeet>4.56</CubicFeet>

            <ListPrice>677</ListPrice>

            <SpecialMQSCode>

              <ProductType>AAZG</ProductType>

              <UnitType>ABEG</UnitType>

            </SpecialMQSCode>

            <ShortDescription>INSERT ASSEMBLY</ShortDescription>

            <OpeningCount>5</OpeningCount>

            <TrackingLines>

              <TrackingNo>0YD749</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:05</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

              <TrackingNo>0YD750</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:05</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

              <TrackingNo>0YD751</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 06:46</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

            </TrackingLines>

          </DETAIL>

          <DETAIL>

            <OrderNumber>SBC00999</OrderNumber>

            <LineNumber>5</LineNumber>

            <OrderType>STANDARD SALES ORDER</OrderType>

            <OrderStatus>SHIPPED COMPLETE</OrderStatus>

            <OrderDate>23-MAY-2013</OrderDate>

            <JobName>Job1</JobName>

            <QtyOrdered>2</QtyOrdered>

            <QtyShipped>2</QtyShipped>

            <WeekofDelivery>10-JUN-2013</WeekofDelivery>

            <Status>THIS TRUCK</Status>

            <CustomerNumber>5000-000</CustomerNumber>

            <CustomerName>CUSTNAME1</CustomerName>

            <CustomerPONumber>W163409</CustomerPONumber>

            <CubicFeet>4.56</CubicFeet>

            <ListPrice>677</ListPrice>

            <SpecialMQSCode>

              <ProductType>AAZG</ProductType>

              <UnitType>ABEG</UnitType>

            </SpecialMQSCode>

            <ShortDescription>INSERT ASSEMBLY</ShortDescription>

            <OpeningCount>5</OpeningCount>

            <TrackingLines>

              <TrackingNo>0YD752</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:05</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

              <TrackingNo>0YD753</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:42</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

            </TrackingLines>

          </DETAIL>

          <DETAIL>

            <OrderNumber>SBC01011</OrderNumber>

            <LineNumber>1</LineNumber>

            <OrderType>STANDARD SALES ORDER</OrderType>

            <OrderStatus>SHIPPED COMPLETE</OrderStatus>

            <OrderDate>28-MAY-2013</OrderDate>

            <JobName>Job2</JobName>

            <QtyOrdered>4</QtyOrdered>

            <QtyShipped>4</QtyShipped>

            <WeekofDelivery>10-JUN-2013</WeekofDelivery>

            <Status>THIS TRUCK</Status>

            <CustomerNumber>5000-000</CustomerNumber>

            <CustomerName>CUSTNAME1</CustomerName>

            <CustomerPONumber>W163766</CustomerPONumber>

            <CubicFeet>4.6</CubicFeet>

            <ListPrice>823</ListPrice>

            <SpecialMQSCode>

              <ProductType>AAZG</ProductType>

              <UnitType>ABEG</UnitType>

            </SpecialMQSCode>

            <ShortDescription>INSERT ASSEMBLY</ShortDescription>

            <OpeningCount>5</OpeningCount>

            <TrackingLines>

              <TrackingNo>0YV016</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:46</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

              <TrackingNo>0YV017</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:25</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

              <TrackingNo>0YV018</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 06:51</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

              <TrackingNo>0YV019</TrackingNo>

              <TrackingStatus>Shipped</TrackingStatus>

              <ScanData>

                <ScanDate>11-JUN-2013 07:22</ScanDate>

                <Signature>mark</Signature>

                <ScanStatus>SCANNED</ScanStatus>

              </ScanData>

            </TrackingLines>

          </DETAIL>

        </DETAILS>

      </C52R09>

       

      I would appreciate any help and/or insights from others more experienced in this than I.

      Thanks in advance,

      Paul

        • 1. Re: Help - Nested XMLQuery (XQuery) Syntax
          odie_63

          Hi,

           

          First of all, I don't see any namespace in your sample XML document, is that a mistake?

           

          Also I'm not sure which output you need, a document or a collection of fragments?

           

          Your requirement is a little tricky since it dictates us to look at the details before retrieving non-relative nodes.

          Here's an example :

          select tlid

               , XMLQuery(

                   'let $dtls := /C52R09/DETAILS/DETAIL[OrderNumber=$order_no]

                    return if ($dtls) then

                    <Doc>

                    {

                      <Headers>{ /C52R09/HEADER1, /C52R09/HEADER2 }</Headers>

                    , <Details>{ $dtls }</Details>

                    }

                    </Doc>

                    else ()'

                   PASSING xml_doc

                         , 'SBC00999' as "order_no"

                   RETURNING CONTENT

                 ) detail

          from xml_truck_info

          where tlid = '424500'

            and xmlexists(

                  '/C52R09/HEADER1[TruckNumber=$truck_no]'

                  PASSING xml_doc

                        , '424500' as "truck_no"

                )

          ;

          It should return a document like this :

          <Doc>

          <Headers>

            <HEADER1> ...

            <HEADER2> ...

          </Headers>

          <Details>

            <DETAIL> ...

            <DETAIL> ...

            <DETAIL> ...

          </Details>

          </Doc>

          • 2. Re: Help - Nested XMLQuery (XQuery) Syntax
            PK_UpNorth

            Thank you odie_63 for the help and the quick response.

             

            Regarding your questions:

                No, the XML specifies no name space.  The XML is for internal use and to my understanding a name space shouldn't be needed.

                Output as a collection of fragments rather than as a complete document is fine as long as it keeps the basic element requirements of 1 header1, 1 header2 for any number of detail sections return per each xml_document.  The resulting data will be massaged later.

             

            I tried the example you provided above in SQLDeveloper ( I needed to add the  default schema decalration back in).  It did not spit up with an error, and returned a TLID value from the table; however the detail comes back as null.  It didn't find the XML fragments based on the query.  I removed the xmlexists expression in the where clause and got the same result, which looks like it narrows it down to the XMLQuery construction.

             

            Thanks again.

            • 3. Re: Help - Nested XMLQuery (XQuery) Syntax
              odie_63

              I needed to add the  default schema decalration back in

              Why did you do that?

              As I said, there's no default namespace in the input document, so there's nothing to declare in the XQuery expression.

               

              (and BTW, there's a typo here : xsi:noNamespaceSchemaLocation)

              • 4. Re: Help - Nested XMLQuery (XQuery) Syntax
                PK_UpNorth

                Sorry, as I look back at the declaration in the root element included in the example, I must have used an older version of the data.  The root element with declarations should have been this:

                <C52R09 xmlns="http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd"

                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                xsi:SchemaLocation="http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd                                 

                http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd">


                I've included the query with the declaration (using data values in the table, but different from those in the example) and the result below:


                 

                PK_XML-MOS2>>select tlid

                  2       , XMLQuery('declare default element namespace "http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd"; (::)

                  3           let $dtls := /C52R09/DETAILS/DETAIL[OrderNumber=$order_no]

                  4            return if ($dtls) then

                  5            <Doc>

                  6            {

                  7              <Headers>{ /C52R09/HEADER1, /C52R09/HEADER2 }</Headers>

                  8            , <Details>{ $dtls }</Details>

                  9            }

                10            </Doc>

                11            else ()'

                12           PASSING xml_doc

                13                 , '17Z00266' as "order_no"

                14           RETURNING CONTENT

                15         ).getClobVal() detail

                16  from xml_truck_info

                17  where tlid = 398043

                18    and xmlexists('declare default element namespace "http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd"; (::)

                19          /C52R09/HEADER1[TruckNumber=$truck_no]'

                20          PASSING xml_doc

                21                , '398043' as "truck_no"

                22        );

                More .....

                 

                      TLID  DETAIL

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

                    398043   



                I've included the query without the declaration (same data values as above) and the result below:


                PK_XML-MOS2>>select tlid

                   2       , XMLQuery('let $dtls := /C52R09/DETAILS/DETAIL[OrderNumber=$order_no]

                   3            return if ($dtls) then

                   4            <Doc>

                   5            {

                   6              <Headers>{ /C52R09/HEADER1, /C52R09/HEADER2 }</Headers>

                   7            , <Details>{ $dtls }</Details>

                   8            }

                   9            </Doc>

                  10            else ()'

                  11           PASSING xml_doc

                  12                 , '17Z00266' as "order_no"

                  13           RETURNING CONTENT

                  14         ).getClobVal() detail

                  15  from xml_truck_info

                  16  where tlid = 398043

                  17    and xmlexists('/C52R09/HEADER1[TruckNumber=$truck_no]'

                  18          PASSING xml_doc

                  19                , '398043' as "truck_no"

                  20        );

                 

                no rows selected


                The first example, with the declaration, returns the TLID value, although no detail section.  Whereas, the second, without the declaration, finds nothing to return.


                Thanks again.


                • 5. Re: Help - Nested XMLQuery (XQuery) Syntax
                  Jason_(A_Non)

                  Maybe I missed a change in the requirements but wouldn't something simple like

                  SELECT xt.header1, xt.header2
                    FROM xml_truck_info xti,
                         XMLTable(XMLNamespaces(default 'http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd'),
                                    '/C52R09'
                                  PASSING xti.xml_doc
                                  COLUMNS
                                  header1     XMLTYPE PATH 'HEADER1',
                                  header2     XMLTYPE PATH 'HEADER2',
                                  truck_no    INTEGER PATH 'HEADER1/TruckNumber',
                                  order_first VARCHAR2(15) PATH 'DETAILS/DETAIL[1]/OrderNumber') xt
                   WHERE xti.tlid = 424500
                     AND xti.tlid = xt.truck_no
                     AND xt.order_first IS NOT NULL;
                  

                  work for you as well?  I didn't think you needed to see the DETAILS node, but maybe that changed and I missed it reading the thread.

                  • 6. Re: Help - Nested XMLQuery (XQuery) Syntax
                    odie_63

                    Paul,

                     

                    If I only replace the root element from your original sample with the namespace-qualified one, then the following works as expected :

                    SQL> select tlid

                      2       , XMLSerialize(document

                      3           XMLQuery(

                      4             'declare namespace ns0 = "http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd"; (::)

                      5              let $dtls := /ns0:C52R09/ns0:DETAILS/ns0:DETAIL[ns0:OrderNumber=$order_no]

                      6              return if ($dtls) then

                      7              <Doc>

                      8              {

                      9                <Headers>{ /ns0:C52R09/ns0:HEADER1, /ns0:C52R09/ns0:HEADER2 }</Headers>

                    10              , <Details>{ $dtls }</Details>

                    11              }

                    12              </Doc>

                    13              else ()'

                    14             PASSING xml_doc

                    15                   , 'SBC00999' as "order_no"

                    16             RETURNING CONTENT

                    17           )

                    18           as clob indent

                    19         ) detail

                    20  from xml_truck_info

                    21  where tlid = '424500'

                    22    and xmlexists(

                    23          'declare default element namespace "http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd"; (::)

                    24          /C52R09/HEADER1[TruckNumber=$truck_no]'

                    25          PASSING xml_doc

                    26                , '424500' as "truck_no"

                    27        )

                    28  ;

                     

                          TLID DETAIL

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

                        424500 <Doc>

                                 <Headers>

                                   <HEADER1 xmlns="http://abhist.acme.com:8080/acme/schema/C52R09_v2.xsd">

                                     <TruckNumber>424500</TruckNumber>

                                     <StopID>16</StopID>

                                     <ShipFrom>CITYNAME</ShipFrom>

                                     <ShippingDate>10-JUN-2013</ShippingDate>

                                     <PlannedDepartureDate>10-JUN-2013 05:00</PlannedDepartureDate>

                                     <ETADate>11-JUN-2013</ETADate>

                                     <LoadName>SW 29</LoadName>

                                     <StopName>16-FREEPORT, </StopName>

                                     <StopComment/>

                     

                     

                    Is there something else that's changed in the XML content, such as a namespace redeclaration ?

                    • 7. Re: Help - Nested XMLQuery (XQuery) Syntax
                      PK_UpNorth

                      Jason,

                           Thank you for the input.  I appreciate the alternate approach using XMLTable rather than XMLQuery.  Yes, this works as well, except that it returns only the 2 Header sections and not the one or more Detail section(s).  However, I believe this would be easy enough to modify to that end.  It's a great place to start.

                      • 8. Re: Help - Nested XMLQuery (XQuery) Syntax
                        PK_UpNorth

                        odie_63,

                             Thanks.  I greatly appreciate your help and patience on this.  Yes, you're correct, something else changed in the XML.  I apologize for the oversight.  Because this example was taken from business production data, for business privacy I changed the OrderNumber tag from something more that was more explicit in business terms.  When I ran the query you provided against production data I neglected to change the tag name back; consequently, nothing matched.

                             Both this last query and the original one you suggested, with the namespace declaration and the correct tag name, provided the results I was looking for.  This has been a great help in both the solution and in my understanding of the XMLQuery / XQuery syntax.

                         

                        Regards,

                        Paul