Solved

SQL query to produce XML

Posted on 2014-07-22
1
241 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

13 Experts available now in Live!

Get 1:1 Help Now