Finding duplicates and returning them with related information in XML

Question

The following query returns all orders with a given order number. How do I re-write it so that the specified duplicate order IDs are returned along with their respective custid and/or company name?

DECLARE @x AS XML;
SET @x = N'
<CustomersOrders>
  <Customer custid="1">
     <companyname>Customer NABCD</companyname>
       <Order orderid="10692">
         <orderdate>2013-11-03</orderdate>
       </Order>
        <Order orderid="10702">
          <orderdate>2007-10-13</orderdate>
        </Order>
        <Order orderid="10693">
          <orderdate>2008-03-16</orderdate>
        </Order>
      </Customer>
      <Customer custid="2">
        <companyname>Customer HMKLI</companyname>
        <Order orderid="10308">
          <orderdate>2006-09-18</orderdate>
        </Order>
        <Order orderid="10693">
          <orderdate>2008-03-04</orderdate>
        </Order>
  </Customer>
</CustomersOrders>';

SELECT @x.query('//Order[@orderid=10693]')

EDIT:

Output format would be either...

<CustomersOrders>
    <Customer custid="1">
         <companyname>Customer NABCD</companyname>
            <Order orderid="10693"/>
    </Customer>
    <Customer custid="2">
          <companyname>Customer HMKLI</companyname>
             <Order orderid="10693"/>
    </Customer>
</CustomersOrders>

OR (even better)

  <CustomersOrders>
    <Order orderid="10693">
        <Customer custid="1">
             <companyname>Customer NABCD</companyname>
        </Customer>
        <Customer custid="2">
              <companyname>Customer HMKLI</companyname>
        </Customer>
    </Order>
  </CustomersOrders>'

Given that sample XML, what should be the output *exactly*, can you post the expected output? — har07, Jul 22 '15 at 11:49
Both of the expected output are not well-formed (some element aren't closed properly) — har07, Jul 22 '15 at 12:36

score 1 · Accepted Answer · answered Jul 22 '15 at 13:13

This is one possible way :

SELECT @x.query('
    <CustomersOrders>
    {
        for $order in //Order[@orderid=10693]
        let $customer := $order/..
        return 
            <Customer custid="{$customer/@custid}">
                {$customer/companyname}
                <Order orderid="{$order/@orderid}"/>
            </Customer>
    }
    </CustomersOrders>
')

output :

<CustomersOrders>
  <Customer custid="1">
    <companyname>Customer NABCD</companyname>
    <Order orderid="10693" />
  </Customer>
  <Customer custid="2">
    <companyname>Customer HMKLI</companyname>
    <Order orderid="10693" />
  </Customer>
</CustomersOrders>

Finding duplicates and returning them with related information in XML

1 Answers1