Solved

Select query with FOR XML clause

Posted on 2009-07-01
2
195 Views
Last Modified: 2012-05-07
I am trying to create an XLM file with nodes.  The data is in two tables a Header and Detail table. I have combined those to make a view that looks like.

Customer      CustomerPO OrderDate     StockCode    OrderQty
BOB              10                 01-Jul-09       112-1            5
BOB              10                 02-Jul-09       111-1            3
BOB              10                 03-Jul-09       118-1            2
BOB              10                 04-Jul-09       114-1            1
FRED             NEW           05-Jul-09       118-1            6
FRED             NEW           06-Jul-09       114-1            2
...                   ...                  ...                   ...                 ...

Is there a way to use the FOR XML in SQLServer to get the data to look like the xml below?  Or is there some other utility I could use.

<Orders>

  <OrderHeader>

    <Customer>BOB</Customer>

    <CustomerPoNumber>10</CustomerPoNumber>

    <OrderDate>2009-07-01</OrderDate>

  </OrderHeader>

  <OrderDetails>

    <StockLine>

      <StockCode>112-1</StockCode>

      <OrderQty>5.000</OrderQty>

    </StockLine>

    <StockLine>

      <StockCode>111-1</StockCode>

      <OrderQty>3.000</OrderQty>

    </StockLine>

    <StockLine>

      <StockCode>118-1</StockCode>

      <OrderQty>2.000</OrderQty>

    </StockLine>

    <StockLine>

      <StockCode>114-1</StockCode>

      <OrderQty>1.000</OrderQty>

    </StockLine>

  </OrderDetails>

  <OrderHeader>

    <Customer>FRED</Customer>

    <CustomerPoNumber>FRED</CustomerPoNumber>

    <OrderDate>2009-07-01</OrderDate>

  </OrderHeader>

  <OrderDetails>

    <StockLine>

      <StockCode>118-1</StockCode>

      <OrderQty>6.000</OrderQty>

    </StockLine>

    <StockLine>

      <StockCode>114-1</StockCode>

      <OrderQty>2.000</OrderQty>

    </StockLine>

  </OrderDetails>

</Orders>

Open in new window

0
Comment
Question by:vbchewie
2 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24757846
Something like this:
(you could try doing the conversion from original join as basically you want to get the details as a subquery for xml)
select Customer AS "OrderHeader/Customer"

, CustomerPO AS "OrderHeader/CustomerPO"

, MIN(OrderDate) AS "OrderHeader/OrderDate"

, CAST((SELECT StockCode AS "StockCode", CAST(OrderQty AS MONEY) AS "OrderQty" FROM tbl b WHERE tbl.Customer = b.Customer and tbl.CustomerPO = b.CustomerPO FOR XML PATH('StockLine'), ROOT('OrderDetails')) AS XML)

from tbl

group by Customer, CustomerPO

for xml path('Order'), root('Orders')

Open in new window

0
 
LVL 1

Author Closing Comment

by:vbchewie
ID: 31598931
Perfect! Thank you.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A short film showing how OnPage and Connectwise integration works.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now