SQLTeam.com | Weblogs | Forums

Export data in specific XML format

Hello,

I have this database that needed to be exported in specific XML format.
I'm giving you an example of one table to be more specific.

CREATE TABLE [dbo].[TEST](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Code] [int] NULL,
	[A] [decimal](6, 2) NULL,
	[B] [decimal](6, 2) NULL,
	[C] [decimal](6, 2) NULL,
	[D] [decimal](6, 2) NULL,
	[E] [decimal](6, 2) NULL,
	[F] [decimal](6, 2) NULL,
	[G] [decimal](6, 2) NULL,
	[H] [decimal](6, 2) NULL,
 CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED([Id] ASC))

Each column belongs to a group. Group A (A,B,C,D), group B (E,F), group C(G,H).

The XML output must be like the following:

<table t="TEST">
	<group g="A">
		<u val=**column code value**>
			<col c="A">
				<value>**value of column A**</value>
			</col>
			<col c="B">
				<value>**value of column B**</value>
			</col>
			<col c="C">
				<value>**value of column C**</value>
			</col>
			<col c="D">
				<value>**value of column D**</value>
			</col>
		</u>
	</group>
	<group g="B">
		<u val=**column code value**>
			<col c="E">
				<value>**value of column E**</value>
			</col>
			<col c="F">
				<value>**value of column F**</value>
			</col>				
		</u>
	</group>
	<group g="C">
		<u val=**column code value**>
			<col c="G">
				<value>**value of column G**</value>
			</col>
			<col c="H">
				<value>**value of column H**</value>
			</col>				
		</u>
	</group>
</table>

Is it possible to create the XML based on the above scenario in database side?

Thank you in advance.

Here is something to get you started. It does not pick up all the columns, and it is probably not the shortest; it is simple and mechanical rather than clever and short.

 INSERT INTO dbo.Test VALUES
 (1, 21, 22.7, 24, 257, 3,7,9,1),
 (2, 31, 32.7, 44, 557, 6,9,11,61)

 SELECT 
	'TEST' AS [table/@t],
	(
		SELECT
			'A' AS [group/@g],
			(
				SELECT
					Code AS [u/@val],
					'A' AS [col/@c],
					A AS [col/value]
				FROM
					dbo.Test t2
				WHERE
					t2.Code = t1.Code
				FOR XML PATH(''),TYPE 
			) [group]
		FROM
			dbo.test t1
		FOR XML PATH(''), TYPE
	) AS [table]
FOR XML PATH('');

Thank you @JamesK for the answer, that's what I had in mind of doing but because I have a lot of tables (and many more groups) I was thinking if there is a more dynamic way of doing it.

What if I create a table like the following, any ideas if this can work somehow?

CREATE TABLE [dbo].[COLUMN_GROUP](

[TableName] nvarchar NULL,
[ColumnName] nvarchar NULL,
[GroupName] nvarchar NULL
)

INSERT INTO dbo.COLUMN_GROUP VALUES
('TEST','A','A'),('TEST','B','A'),
('TEST','C','A'),('TEST','D','A'),
('TEST','E','B'),('TEST','F','B'),
('TEST','G','C'),('TEST','H','C')

This is what I have done so far:

SELECT
 'A' as [@g],
 (SELECT
 Code [@val],
 (SELECT
 name as [col/@c],
 [name] as[col/value]
 FROM sys.columns CROSS JOIN dbo.TEST
 WHERE object_id = OBJECT_ID('dbo.TEST')
 and (name='A' or name='B' or name='C' or name='D')
 and Code=1 
 ORDER BY column_id
 FOR XML PATH(''),TYPE)
 FROM dbo.TEST
 WHERE Code=1 
 FOR XML PATH('u'),TYPE
 )
 FROM dbo.TEST 
 WHERE Code=1 
 FOR XML PATH('group'),TYPE

I get the format I want but instead of the value of the column in I get the name. Any ideas how to get the value?
Thank you.

Instead of using name in the line that reads " [name] as[col/value]", use the actual column name, for example like this:

....
SELECT   
	name AS [col/@c] ,
    CASE name
		WHEN 'A' THEN A
		WHEN 'B' THEN B
		WHEN 'C' THEN C
		WHEN 'D' THEN D
	END  AS [col/value]
FROM
	sys.columns
	CROSS JOIN dbo.TEST
....

Thank you @JamesK for all the help.

Here's my code that gets all groups.

  SELECT
'TEST' AS [table/@t],
(
SELECT
GroupName as [@g],
(SELECT
Code [@val],
(SELECT
ColumnName as [col/@c],
CASE name
		WHEN 'A' THEN A
		WHEN 'B' THEN B
		WHEN 'C' THEN C
		WHEN 'D' THEN D
		WHEN 'E' THEN E
		WHEN 'F' THEN F
		WHEN 'G' THEN G
		WHEN 'H' THEN H
	END  AS [col/value]
FROM sys.columns 
  CROSS JOIN TEST c1
  INNER JOIN COLUMN_GROUP r1
	  ON sys.columns.name=r1.columnname
WHERE object_id = OBJECT_ID('TEST')
and r1.GroupName=r2.GroupName  and  c1.Id=c2.Id
ORDER BY column_id
FOR XML PATH(''),TYPE)
FROM TEST c2
FOR XML PATH('u'),TYPE
)
FROM sys.columns 
  CROSS JOIN TEST c3
  INNER JOIN COLUMN_GROUP r2
	  ON sys.columns.name=r2.columnname
WHERE object_id = OBJECT_ID('TEST') 
GROUP BY GroupName
FOR XML PATH('group'),TYPE
)AS [table]
FOR XML PATH('')