Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Angry Unanswered: XML change from 2000 to 2005

    I have this stored proc in 2000:

    Code:
    SELECT
    
    YEAR(a.DateCreated) AS OrderYear, MONTH(a.DateCreated) AS OrderMonth, OrderDescription, OrderType, OrderId, DateCreated, OrderStatusId, OrderStatus
    
    FROM
    
    (	--Get stock orders
    
    	SELECT
    		o.OrderId,
    		o.DateCreated,
    		'Stock Order' AS OrderDescription,
    		1 AS OrderType,
    		o.OrderStatusId,
    		os.OrderStatus,
    		o.OrderTotal
    	FROM
    		[Order] o INNER JOIN OrderStatus os on o.OrderStatusId = os.OrderStatusId
    	GROUP BY 
    		YEAR(o.DateCreated), MONTH(o.DateCreated), o.DateCreated,  o.OrderId, o.OrderStatusId, os.OrderStatus
    
    union
    	--Get BizCard orders
    	SELECT 
    		bc.BizCardOrderId AS OrderId,
    		bc.DateCreated,
    		'Biz Card Order' AS OrderDescription,
    		2 AS OrderType,
    		bc.OrderStatusId,
    		os.OrderStatus,
    		bc.OrderTotal
    	FROM 
    		BizCardOrder bc INNER JOIN OrderStatus os on bc.OrderStatusId = os.OrderStatusId
    	GROUP BY 
    		YEAR(bc.DateCreated), MONTH(bc.DateCreated), bc.DateCreated,  bc.BizCardOrderId, bc.OrderStatusId, os.OrderStatus
    
    ) AS a
    
    ORDER BY
    	YEAR(a.DateCreated) DESC, MONTH(a.DateCreated) DESC, OrderDescription, OrderId, OrderTotal
    	
    FOR XML AUTO

    And in 2000 the output is similar to this:

    Code:
    <a OrderYear="2008" OrderMonth="9" orderdescription="Biz Card Order" ordertype="2">
      <o orderId="53165" dateCreated="09/16/2008" statusId="10">
        <OrderStatus orderstatus="ORDER DATE + 45 DAYS" />
      </o>
    </a>
    <a OrderYear="2008" OrderMonth="9" orderdescription="Stock Order" ordertype="1">
      <o orderId="1" dateCreated="09/17/2008" statusId="9">
        <OrderStatus orderstatus="SENT TO PRESS" />
      </o>
      <o orderId="2" dateCreated="09/17/2008" statusId="9">
        <OrderStatus orderstatus="SENT TO PRESS" />
      </o>
    </a>
    Notice there are 2 "o" nested under the 2nd "a" element

    Now in 2005 the results are not nested:


    Code:
    <a OrderYear="2008" OrderMonth="9" orderdescription="Biz Card Order" ordertype="2" orderId="53165" dateCreated="09/16/2008" statusId="10" orderstatus="ORDER DATE + 45 DAYS" />
    <a OrderYear="2008" OrderMonth="9" orderdescription="Stock" ordertype="1" orderId="1" dateCreated="09/17/2008" statusId="1" orderstatus="PROCESSING" />
    <a OrderYear="2008" OrderMonth="9" orderdescription="Stock" ordertype="1" orderId="2" dateCreated="09/17/2008" statusId="1" orderstatus="PROCESSING" />

    How can I resolve this? I need the XML nested like before.

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    You can add FOR ELEMENTS to your XML clause.

  3. #3
    Join Date
    Mar 2003
    Posts
    130
    Quote Originally Posted by Peso
    You can add FOR ELEMENTS to your XML clause.
    That only changes the attributes to nested attributes, it doesn't fix the nesting problem.

    So with ELEMENTS the behavior in 2000 is to return:

    Code:
    <a>
      <OrderYear>2008</OrderYear>
      <OrderMonth>9</OrderMonth>
      <orderdescription>Biz Card</orderdescription>
      <ordertype>2</ordertype>
      <o>
        <orderId>53165</orderId>
        <dateCreated>09/16/2008</dateCreated>
        <statusId>10</statusId>
        <OrderStatus>
          <orderstatus>ORDER DATE + 45 DAYS</orderstatus>
        </OrderStatus>
      </o>
    </a>
    <a>
      <OrderYear>2008</OrderYear>
      <OrderMonth>9</OrderMonth>
      <orderdescription>Print On Demand</orderdescription>
      <ordertype>3</ordertype>
      <o>
        <orderId>1</orderId>
        <dateCreated>09/17/2008</dateCreated>
        <statusId>9</statusId>
        <OrderStatus>
          <orderstatus>SENT TO PRESS</orderstatus>
        </OrderStatus>
      </o>
      <o>
        <orderId>2</orderId>
        <dateCreated>09/17/2008</dateCreated>
        <statusId>9</statusId>
        <OrderStatus>
          <orderstatus>SENT TO PRESS</orderstatus>
        </OrderStatus>
      </o>
    </a>
    And once again "o" can be nested multiple times under "a" but on 2005 it's like this:

    Code:
    <a>
      <OrderYear>2008</OrderYear>
      <OrderMonth>9</OrderMonth>
      <orderdescription>Biz Card</orderdescription>
      <ordertype>2</ordertype>
      <orderId>53165</orderId>
      <dateCreated>09/16/2008</dateCreated>
      <statusId>10</statusId>
      <orderstatus>ORDER DATE + 45 DAYS</orderstatus>
    </a>
    <a>
      <OrderYear>2008</OrderYear>
      <OrderMonth>9</OrderMonth>
      <orderdescription>Print On Demand</orderdescription>
      <ordertype>3</ordertype>
      <orderId>1</orderId>
      <dateCreated>09/17/2008</dateCreated>
      <statusId>9</statusId>
      <orderstatus>SENT TO PRESS</orderstatus>
    </a>

  4. #4
    Join Date
    Mar 2003
    Posts
    130
    It would help if I knew what type of query this is to see if I can rewrite it.
    It doesn't seem to be a sub-query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •