Solved

SQL query to produce XML

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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