FOR XML Explicit clause in SQL Server 2000


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: FOR XML Explicit clause in SQL Server 2000

Hybrid View

  1. #1
    reddy Guest

    FOR XML Explicit clause in SQL Server 2000


    Pls help be optimize...

    this is my required xml structure ....
    "<DataSet><Customers>
    <Record><CustomerID>ALFKI</CustomerID><CompanyName>Alfreds Futterkiste</CompanyName><Orders><Record><OrderID>10643</OrderID></Record><Record><OrderID>10692</OrderID></Record><Record><OrderID>10702</OrderID></Record><Record><OrderID>10835</OrderID></Record>
    <Record><OrderID>10952</OrderID></Record><Record><OrderID>11011</OrderID></Record></Orders></Record>
    </Customers></DataSet>"

    the query used was ...
    SELECT
    1 AS TAG,
    NULL AS PARENT,
    NULL As [DataSet!1],
    NULL As [Customers!2],
    NULL AS [Record!3!CustomerID!element],
    NULL AS [Record!3!CompanyName!element],
    NULL As [Orders!4],
    NULL AS [Record!5!OrderID!element]
    UNION ALL
    SELECT
    2 AS TAG,
    1 AS PARENT,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
    UNION ALL
    SELECT
    3 AS TAG,
    2 AS PARENT,
    NULL,
    NULL,
    Customers.CustomerID,
    Customers.CompanyName,
    NULL,
    NULL
    FROM Customers
    where Customers.customerID like 'ALFKI'
    UNION ALL
    SELECT
    4 AS TAG,
    3 AS PARENT,
    NULL ,
    NULL,
    Customers.CustomerID,
    NULL,
    NULL,
    NULL
    FROM Customers
    where Customers.customerID like 'ALFKI'
    UNION ALL
    SELECT
    5 AS TAG,
    4 AS PARENT,
    NULL,
    NULL,
    Customers.CustomerID,
    Null,
    NULL,
    Orders.OrderID
    FROM Customers, Orders
    WHERE Customers.CustomerID = Orders.CustomerID and customers.customerID like
    'ALFKI'
    ORDER BY [Record!3!CustomerID!element],[Record!5!OrderID!element]
    FOR XML EXPLICIT

    in Northwind db in sql 2000

    to get the tag or orders i make an unnecessary query... can i avoid that?
    if so how ?


  2. #2
    Jason Rein Guest

    Re: FOR XML Explicit clause in SQL Server 2000


    Try this:

    SELECT
    1 AS TAG,
    NULL AS PARENT,
    NULL As [DataSet!1!Customers!element],
    NULL AS [Record!2!CustomerID!element],
    NULL AS [Record!2!CompanyName!element],
    NULL As [Orders!3],
    NULL AS [Record!4!OrderID!element]
    UNION ALL
    SELECT
    2 AS TAG,
    1 AS PARENT,
    NULL,
    Customers.CustomerID,
    Customers.CompanyName,
    NULL,
    NULL
    FROM Customers
    where Customers.customerID like 'ALFKI'
    UNION ALL
    SELECT
    3 AS TAG,
    2 AS PARENT,
    NULL,
    Customers.CustomerID,
    NULL,
    NULL,
    NULL
    FROM Customers
    where Customers.customerID like 'ALFKI'
    UNION ALL
    SELECT
    4 AS TAG,
    3 AS PARENT,
    NULL,
    Customers.CustomerID,
    Null,
    NULL,
    Orders.OrderID
    FROM Customers, Orders
    WHERE Customers.CustomerID = Orders.CustomerID and customers.customerID like
    'ALFKI'
    ORDER BY [Record!2!CustomerID!element],[Record!4!OrderID!element]
    FOR XML EXPLICIT

    - Jason Rein

    "reddy" <narendrareddy@hotmail.com> wrote:
    >
    >Pls help be optimize...
    >
    >this is my required xml structure ....
    >"<DataSet><Customers>
    ><Record><CustomerID>ALFKI</CustomerID><CompanyName>Alfreds Futterkiste</CompanyName><Orders><Record><OrderID>10643</OrderID></Record><Record><OrderID>10692</OrderID></Record><Record><OrderID>10702</OrderID></Record><Record><OrderID>10835</OrderID></Record
    ><Record><OrderID>10952</OrderID></Record><Record><OrderID>11011</OrderID></Record></Orders></Record>
    ></Customers></DataSet>"
    >
    >the query used was ...
    >SELECT
    > 1 AS TAG,
    > NULL AS PARENT,
    > NULL As [DataSet!1],
    > NULL As [Customers!2],
    > NULL AS [Record!3!CustomerID!element],
    > NULL AS [Record!3!CompanyName!element],
    > NULL As [Orders!4],
    > NULL AS [Record!5!OrderID!element]
    >UNION ALL
    >SELECT
    > 2 AS TAG,
    > 1 AS PARENT,
    > NULL,
    > NULL,
    > NULL,
    > NULL,
    > NULL,
    > NULL
    >UNION ALL
    >SELECT
    > 3 AS TAG,
    > 2 AS PARENT,
    > NULL,
    > NULL,
    > Customers.CustomerID,
    > Customers.CompanyName,
    > NULL,
    > NULL
    >FROM Customers
    >where Customers.customerID like 'ALFKI'
    >UNION ALL
    >SELECT
    > 4 AS TAG,
    > 3 AS PARENT,
    > NULL ,
    > NULL,
    > Customers.CustomerID,
    > NULL,
    > NULL,
    > NULL
    >FROM Customers
    >where Customers.customerID like 'ALFKI'
    >UNION ALL
    >SELECT
    > 5 AS TAG,
    > 4 AS PARENT,
    > NULL,
    > NULL,
    > Customers.CustomerID,
    > Null,
    > NULL,
    > Orders.OrderID
    >FROM Customers, Orders
    >WHERE Customers.CustomerID = Orders.CustomerID and customers.customerID

    like
    > 'ALFKI'
    >ORDER BY [Record!3!CustomerID!element],[Record!5!OrderID!element]
    >FOR XML EXPLICIT
    >
    >in Northwind db in sql 2000
    >
    >to get the tag or orders i make an unnecessary query... can i avoid that?
    >if so how ?
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center