?
Solved

SQL query to produce XML

Posted on 2014-07-22
1
Medium Priority
?
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

801 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