SQL Server 2008 - Need column names as an attribute

Question

I need to create a T-SQL query in SQL Server 2008 which outputs data as XML. But this is a generic process that queries data with different columns, which could have spaces in the column names. Therefore, I'd like the XML to list the column name as an attribute (spaces aren't allowed in element names).

Given a master table which links to a detail table for child records, I would want the output to look like this:

<master id="123">
   <detail colname="customer">John Smith</detail>
   <detail colname="amount">888.45</detail>
   <detail colname="date">01/01/01</detail>
</master>
<master id="456">
   <detail colname="customer">Suzie Jones</detail>
   <detail colname="amount">1000.25</detail>
   <detail colname="date">05/05/01</detail>
</master>

The columns in the detail record can vary, so I can't hardcode them.

I believe this may be possible using PIVOT command, but that gets really ugly when you don't know structure of data. I feel like there's got to be a way to get a column name to appear as an attribute value!

thanks for any advice.

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Apr 06 '11 at 15:57

score 2 · Answer 1 · answered Dec 28 '11 at 23:43

Using XQuery a set of simple xml rows can be unpivoted into the required format

DECLARE @X xml = '<row id="123" customer="John Smith" amount="888.45" date="01/01/01" />'
                +'<row id="456" customer="Suzie Jones" amount="1000.25" date="05/05/01" />'

SELECT  @X.query
        (
            '
            for     $id in //row/attribute::id
            return  <master id="{ string($id) }">
                        {
                            for $attr in //row[@id=$id]/attribute::*
                            where local-name($attr) != "id"
                            return <detail columnname="{ local-name($attr) }">{ string($attr) }</detail>
                        }
                    </master>
            '
        )

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

Use the FOR XML clause in T-SQL

more examples here

Update

For the OP: use FOR XML EXPLICIT to explicitly set attribute values from columns.

Other EXPLICIT examples:

http://blogs.technet.com/b/wardpond/archive/2006/09/08/454938.aspx

edited May 23 '17 at 11:49

Community

1
1

answered Apr 06 '11 at 15:52

Mike Marshall

7,788
4
39
63

If you have an example of where FOR XML makes the column name an attribute without hardcoding, I would love to see it. Thanks. – KTH Apr 06 '11 at 17:40
Thanks MJ, but I still don't think this does what I need. I need the NAME of the column to be an attribute. firstname is the column name in table I'm querying. BUT I can't hardcode firstname - I'm querying a table dynamically and the columns are different on each execution. – KTH Apr 06 '11 at 21:25

score 0 · Answer 3 · answered Apr 06 '11 at 15:53

0

Is there any way you can use "FOR XML" in your query? This does a lot of the heavy lifting for you.

This page details something very similar to what I think you're after: http://msdn.microsoft.com/en-us/library/ms345137(v=sql.90).aspx

answered Apr 06 '11 at 15:53

NateTheGreat

2,295
13
9

I'm using FOR XML. But the problem is getting the column names to appear as an attribute, rather than an element (and the column names change, so they can't be hardcoded) – KTH Apr 06 '11 at 17:40

score 0 · Answer 4 · answered Apr 06 '11 at 16:17

You can create a stored procedure to do that as follows:

if exists (select * from dbo.sysobjects where id = object_id(N'[QueryXML]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure QueryXML
GO
create procedure QueryXML
  @Columns VarChar(1000),
  @Table VarChar(100)
as
begin
  declare @query varchar(1100)

  set @query = 'select (select ' + @Columns + ' from ' + @Table + ' for XML PATH(''columns'')) for XML PATH(''''), ROOT (''SampleXML'')'   

  exec (@query)

end

I tried with these table

create table dummy1(
  ID int not null identity(1,1),
  dummy1 int,
  dummy2 int,
  dummy3 int
)
GO

insert into dummy1 (dummy1,dummy2,dummy3) values(1,2,3)
insert into dummy1 (dummy1,dummy2,dummy3) values(4,5,6)
insert into dummy1 (dummy1,dummy2,dummy3) values(7,8,9)
insert into dummy1 (dummy1,dummy2,dummy3) values(10,11,12)
insert into dummy1 (dummy1,dummy2,dummy3) values(13,14,15)
insert into dummy1 (dummy1,dummy2,dummy3) values(16,17,18)

example:

exec QueryXML @Columns = 'ID, dummy1', @Table = 'dummy1'
exec QueryXML @Columns = 'ID, dummy2,dummy3', @Table = 'dummy1'
exec QueryXML @Columns = 'ID, dummy1,dummy2,dummy3', @Table = 'dummy1'

you can also review: sql-server-simple-example-of-creating-xml-file-using-t-sql

I think you're headed in the right direction, but this didn't work for me running your sample. Output looked like this....<columns><ID>1</ID><dummy1>1< — KTH, Apr 06 '11 at 17:57

SQL Server 2008 - Need column names as an attribute

4 Answers4