Export data in specific XML format

Transact-SQL
, ,
1

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.

2

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('');
3

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')

4

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.

5

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
....
6

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('')