Building an XML output using XML EXPLICIT in SQL Server 2014

Question

I have nearly accomplished what I need, but am missing one last thing. I have 2 tables being joined to create an XML output. I need elements of both tables to be used in the same SELECT statement, but am having trouble making that work. This code is what I have:

SELECT 
    1 as Tag,
    0 as Parent,
    RTRIM(dbo.DataItemInfo.DataItem) as [DataItem!1!name]
    --RTRIM(dbo.DataItemInfo.DataItem) as [dbo.DataSchedule.DataItemValue!1!]
FROM
    dbo.DataItemInfo
INNER JOIN
    dbo.DataSchedule
ON dbo.DataSchedule.SignID = dbo.DataItemInfo.SignID
AND dbo.DataSchedule.SignID=@ParamSignID
AND dbo.DataSchedule.ScheduleID = dbo.DataItemInfo.ScheduleID 
FOR XML EXPLICIT, ROOT('DataItems')

Where the commented out section is one of the things I tried. At the moment, it produces the output:

<DataItems>
  <DataItem name="Test1" />
  <DataItem name="Test2" />
  <DataItem name="Test3" />
  <DataItem name="Test4" />
  <DataItem name="Test5" />
</DataItems>

But I want:

<DataItems>
  <DataItem name="Test1">ValFromScheduleTableHere<DataItem/>
  <DataItem name="Test2">ValFromScheduleTableHere<DataItem/>
  <DataItem name="Test3">ValFromScheduleTableHere<DataItem/>
  <DataItem name="Test4">ValFromScheduleTableHere<DataItem/>
  <DataItem name="Test5">ValFromScheduleTableHere<DataItem/>
</DataItems>

I know how to populate the "ValFromScheduleTableHere" from the original table, but not from a second table. Thanks for the help.

Why don't you use FOR XML PATH? It's typically much easier to understand and get the results you want, compared to FOR XML EXPLICIT... — marc_s
– marc_s, Commented Nov 14, 2014 at 16:21
Perhaps it would have been easier, but once I get this small thing accomplished I'm done. — Cory
– Cory, Commented Nov 14, 2014 at 16:24
I have been working with sql server for almost 9 years, and I have only seen once an XML query using EXPLICITand couldn't make any sense of it, as far as I remember I had to use FOR XML PATH(''), TYPE in the inner select to get the same results. — M.Ali
– M.Ali, Commented Nov 14, 2014 at 16:37

radar · Accepted Answer · 2014-11-14 16:36:45Z

1

FOR XML PATH syntax is easier and you can get the desired result with this SQL.

SELECT
(
SELECT 
    RTRIM(dbo.DataItemInfo.DataItem) as 'DataItem/@name'
    DataSchedule.DataitemValue as 'DataItem'
FROM
    dbo.DataItemInfo
INNER JOIN
    dbo.DataSchedule
ON dbo.DataSchedule.SignID = dbo.DataItemInfo.SignID
AND dbo.DataSchedule.SignID=@ParamSignID
AND dbo.DataSchedule.ScheduleID = dbo.DataItemInfo.ScheduleID 
FOR XML PATH(''), TYPE
)
FOR XML PATH(''), ROOT('DataItems')

answered Nov 14, 2014 at 16:36

radar

13.4k2 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cory Over a year ago

Thats how it's done, thanks! And I don't think I'll ever use XML EXPLICIT again. It was such a headache.

Solomon Rutzky · Accepted Answer · 2014-11-14 17:10:54Z

I realize this has been answered and even that EXPLICIT mode is no longer being used, but I happened to figure it out so I thought I would share. For better or worse, you were very close. The column name for the field to be the entity value was missing the "directive" portion, which is the 4th (and optional) part of the funky column name !-convention. You needed !xml as the last part, and of course to have adjusted what is in the RTRIM and the rest of the alias, which would have looked like:

RTRIM(DataSchedule.DataItemValue) as [DataItem!1!!xml]

Anyone can test the theory with the following (I aligned the column aliases to more easily see the difference in the 3rd alias):

SELECT 1 AS [Tag], 
       0 AS [Parent],
       RTRIM(so.[type]) AS [Object!1!Type],
       so.create_date   AS [Object!1!CreateDate],
       so.[name]        AS [Object!1!!xml]
FROM sys.objects so
FOR XML EXPLICIT, ROOT('Objects');

Output:

<Objects>
  <Object Type="S" CreateTate="2014-02-20T20:48:34.573">sysrscols</Object>
  <Object Type="S" CreateTate="2009-04-13T12:59:05.513">sysrowsets</Object>
  <Object Type="S" CreateTate="2014-02-20T20:48:34.807">sysclones</Object>
  <Object Type="S" CreateTate="2009-04-13T12:59:05.500">sysallocunits</Object>
  <Object Type="S" CreateTate="2003-04-08T09:13:37.267">sysfiles1</Object>
  ...
</Objects>

For more info, please see the following MSDN page: Use EXPLICIT Mode with FOR XML

Collectives™ on Stack Overflow

Building an XML output using XML EXPLICIT in SQL Server 2014

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related