Solved

SQL query to produce XML

Posted on 2014-07-22
1
247 Views
Last Modified: 2014-07-28
Hello,

I have an SQL statement that reads some rows from a couple of tables and produces an xml.  See below:
 declare @xmlData xml ;

  -- Now select all the data for the given id in xml format.
  set @xmlData = 
    ( 
		
    select top 5

       L.[Id],
       L.[UserId],
	   Un.[Name] as [UnitName],
       left([LastLoginDateTime],16) as [LastLoginDateTime] ,
       left( [LastLogoutDateTime], 16) as [LastLogoutDateTime],
       [LastLoginIP],
	   case when [LastLogoutReason] = 1 then 'Loggat ut' when [LastLogoutReason] = 2 then 'Utloggat av systemet' else '' end as [LastLogoutReason]

    from [BRUM_Admin].[dbo].[Logins] L 
    inner join [BRUM_Admin].[dbo].Units Un on l.UnitId = Un.Id
     where userid = 1
      and LastLoginDateTime is not null
    order by LastLoginDateTime desc
 
         
          for xml path ('OriginalData2012'), type)
 
  select @xmlData

Open in new window

and it produces the following xml:
<OriginalData2012>
  <Id>16097</Id>
  <UserId>1</UserId>
  <UnitName>nasher</UnitName>
  <LastLoginDateTime>2014-02-17 21:52</LastLoginDateTime>
  <LastLogoutDateTime>2014-02-17 22:52</LastLogoutDateTime>
  <LastLoginIP>90.213.111.23</LastLoginIP>
  <LastLogoutReason>Loggat ut</LastLogoutReason>
</OriginalData2012>
<OriginalData2012>
  <Id>1</Id>
  <UserId>1</UserId>
  <UnitName>EyeNetAdmin</UnitName>
  <LastLoginDateTime>2013-11-17 21:52</LastLoginDateTime>
  <LastLogoutDateTime>2013-11-17 22:08</LastLogoutDateTime>
  <LastLoginIP>90.230.64.247</LastLoginIP>
  <LastLogoutReason>Utloggat av systemet</LastLogoutReason>
</OriginalData2012>

Open in new window

However i would like to produce an xml like this:
<OriginalData2012>
  <Id1>16097</Id1>
  <UserId1>1</UserId1>
  <UnitName1>nasher</UnitName1>
  <LastLoginDateTime1>2014-02-17 21:52</LastLoginDateTime1>
  <LastLogoutDateTime1>2014-02-17 22:52</LastLogoutDateTime1>
  <LastLoginIP1>90.213.111.23</LastLoginIP1>
  <LastLogoutReason1>Loggat ut</LastLogoutReason1>

  <Id2>1</Id2>
  <UserId2>1</UserId2>
  <UnitName2>EyeNetAdmin</UnitName2>
  <LastLoginDateTime2>2013-11-17 21:52</LastLoginDateTime2>
  <LastLogoutDateTime2>2013-11-17 22:08</LastLogoutDateTime2>
  <LastLoginIP2>90.230.64.247</LastLoginIP2>
  <LastLogoutReason2>Utloggat av systemet</LastLogoutReason2>
</OriginalData2012>

Open in new window

Basically each node name should be qualified with its order in the xml so that they are unique.

Any ideas?

Thanks in advance!
0
Comment
Question by:soozh
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40211151
you cannot get that in the standard sql server "for XML" feature.
the tags are row-based, hence the 2 xml tags.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
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…

786 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