Solved

FOR XML EXPLICIT

Posted on 2011-09-13
2
335 Views
Last Modified: 2012-05-12
Given the attached data, how would I write a select statement FOR XML Explicit. I want the structure to be
<Locations>
  <LocationID=xxx>
  <CinglePointID>xxx</CinglePointID>
   <OPUS_ID>XXX</OPUS_ID>
    etc......
   </LocationID>
  <LocationID=xxxx>
  <CinglePointID>xxx</CinglePointID>
  <OPUS_ID>XXX</OPUS_ID>
     etc...
  </LocationID>
 

</Locations>
 locations.csv
0
Comment
Question by:sherbug1015
2 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36533132
sherbug1015,

How are you?

First, I am not sure you need FOR XML EXPLICIT here. Second, your attachment doesn't have column names, so my "first" is based on assumptions that the data and resulting XML are pretty flat. Another assumption is the first column is your LocationID column and that your first XML element is really <Location ID="xxx">...</Location>.

With all that said, here is what my first attempt would be:
SELECT LocationID AS "@ID"
     , CinglePointID
     , OPUS_ID
     -- and so on ...
FROM Your_TableName
FOR XML PATH('Location'), TYPE, ROOT('Locations')
;

Open in new window

0
 

Author Closing Comment

by:sherbug1015
ID: 36533206
Perfect.  Thanks
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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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