How do I merge two XML variables in SQL Server (2005/2008)?

Question

Forward: I've already read this StackOverflow thread and it does not address the depth retention I need.

I want to create an XML document that contains the data definition objects for a single database, using the hierarchy:

<CATALOG>
 <SCHEMA>
 <TABLE>
 <TableExtendedProperty/>
 <TableExtendedProperty/>
 <COLUMN>
 <ColumnExtendedProperty/>
 <ColumnExtendedProperty/>
 </COLUMN>
 <COLUMN>
 <ColumnExtendedProperty/>
 <ColumnExtendedProperty/>
 </COLUMN>
 ...etc...
 </TABLE>
 ...etc...
 </SCHEMA>
 ...etc...
</CATALOG>

I have two queries constructed.

• One creates the hierarchy to the TABLE level with child TableExtendedProperty elements
• One creates the hierarchy to the COLUMN level with child ColumnExtendedProperty elements

The (abbreviated) XML result for each query are provided below.

I need to either (a) merge these two XML variables into a single XML variable, or (b) construct a different query that produces the desired result.

What can you recommend?

Source Code

Query creating hierarchy to the TABLE level with child TableExtendedProperty elements
SELECT DISTINCT
_Catalog.TABLE_CATALOG
,_Schema.TABLE_SCHEMA
,_Table.TABLE_NAME
,_TableExtProp.name as TableExtPropName
,_TableExtProp.value as TableExtPropValue
from information_schema.tables _Schema
INNER JOIN information_schema.columns _Catalog ON _Catalog.TABLE_CATALOG=_Schema.TABLE_CATALOG
INNER JOIN information_schema.tables _Table ON _Table.TABLE_NAME=_Schema.TABLE_NAME 
LEFT OUTER join sys.extended_properties _TableExtProp on _TableExtProp.major_id=OBJECT_ID(_Schema.TABLE_SCHEMA + '.' + _Table.TABLE_NAME) and _TableExtProp.minor_id=0
FOR XML AUTO
RESULT
<_Catalog TABLE_CATALOG="A">
 <_Schema TABLE_SCHEMA="B">
 <_Table TABLE_NAME="C">
 <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Lorem" />
 <_TableExtProp TableExtPropName="TP2" TableExtPropValue="Ipsum" />
 </_Table>
 <_Table TABLE_NAME="D">
 <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Borem" />
 </_Table>
 </_Schema>
 <_Schema TABLE_SCHEMA="E">
 <_Table TABLE_NAME="F">
 </_Table>
 <_Table TABLE_NAME="G">
 <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Corem" />
 </_Table>
 </_Schema>
</_Catalog>
Query creating hierarchy to the COLUMN level with child ColumnExtendedProperty elements
SELECT DISTINCT
_Catalog.TABLE_CATALOG
,_Schema.TABLE_SCHEMA
,_Table.TABLE_NAME
,_Col.COLUMN_NAME
,_ColumnExtProp.name as ColumnExtPropName
,_ColumnExtProp.value as ColumnExtPropValue
from information_schema.tables _Schema
INNER JOIN information_schema.columns _Catalog ON _Catalog.TABLE_CATALOG=_Schema.TABLE_CATALOG
INNER JOIN information_schema.tables _Table ON _Table.TABLE_NAME=_Schema.TABLE_NAME 
INNER JOIN information_schema.columns _Col ON _col.Table_name=_Table.Table_Name /* need a schema comparison also */
LEFT OUTER join sys.extended_properties _ColumnExtProp on _ColumnExtProp.major_id=OBJECT_ID(_Schema.TABLE_SCHEMA + '.' + _Table.TABLE_NAME) and _ColumnExtProp.minor_id=_Col.ORDINAL_POSITION
FOR XML AUTO
RESULT
 <_Catalog TABLE_CATALOG="A">
 <_Schema TABLE_SCHEMA="B">
 <_Table TABLE_NAME="C">
 <_Col COLUMN_NAME="AXA">
 <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WWW" />
 <_ColumnExtProp ColumnExtPropName="CP2" ColumnExtPropValue="WWW" />
 </_Col>
 <_Col COLUMN_NAME="FRDCQ">
 <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="EQW" />
 </_Col>
 </_Table>
 <_Table TABLE_NAME="D">
 <_Col COLUMN_NAME="PolicyTypeCode">
 <_ColumnExtProp ColumnExtPropName="CP3" ColumnExtPropValue="SAS" />
 </_Col>
 </_Table>
 </_Schema>
 <_Schema TABLE_SCHEMA="E">
 <_Table TABLE_NAME="F">
 <_Col COLUMN_NAME="BAXA">
 </_Col>
 <_Col COLUMN_NAME="BAFA">
 <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WEW" />
 </_Col>
 </_Table>
 <_Table TABLE_NAME="G">
 <_Col COLUMN_NAME="BAFA">
 <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WTTEW" />
 </_Col>
 </_Table>
 </_Schema>
 </_Catalog>
DESIRED RESULT
 <_Catalog TABLE_CATALOG="A">
 <_Schema TABLE_SCHEMA="B">
 <_Table TABLE_NAME="C">
 <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Lorem" />
 <_TableExtProp TableExtPropName="TP2" TableExtPropValue="Ipsum" />
 <_Col COLUMN_NAME="AXA">
 <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WWW" />
 <_ColumnExtProp ColumnExtPropName="CP2" ColumnExtPropValue="WWW" />
 </_Col>
 <_Col COLUMN_NAME="FRDCQ">
 <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="EQW" />
 </_Col>
 </_Table>
 <_Table TABLE_NAME="D">
 <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Borem" />
 <_Col COLUMN_NAME="PolicyTypeCode">
 <_ColumnExtProp ColumnExtPropName="CP3" ColumnExtPropValue="SAS" />
 </_Col>
 </_Table>
 </_Schema>
 <_Schema TABLE_SCHEMA="E">
 <_Table TABLE_NAME="F">
 <_Col COLUMN_NAME="BAXA">
 </_Col>
 <_Col COLUMN_NAME="BAFA">
 <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WEW" />
 </_Col>
 </_Table>
 <_Table TABLE_NAME="G">
 <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Corem" />
 <_Col COLUMN_NAME="BAFA">
 <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WTTEW" />
 </_Col>
 </_Table>
 </_Schema>
 </_Catalog>

Answer 1

You can use for xml path instead of for xml raw. Build your hierarchy in correlated sub-queries in the field list. You can also make use of order by in the sub-queries to have tables ordered alphabetically and columns to order by ORDINAL_POSITION.

I tried to translate what you have and came up with this.

select T1.TABLE_CATALOG as "@TABLE_CATALOG",
 (
 select T2.TABLE_SCHEMA as "@TABLE_SCHEMA",
 (
 select T3.TABLE_NAME as "@TABLE_NAME",
 (
 select P.name as "@TableExtPropName",
 P.value as "@TableExtPropValue"
 from sys.extended_properties as P
 where P.major_id = object_id(T2.TABLE_SCHEMA+'.'+T3.TABLE_NAME) and
 P.minor_id = 0
 for xml path('_TableExtProp'), type
 ),
 (
 select C.COLUMN_NAME as "@COLUMN_NAME",
 (
 select P.name as "@ColumnExtPropName",
 P.value as "@ColumnExtPropValue"
 from sys.extended_properties as P
 where P.major_id = object_id(T2.TABLE_SCHEMA+'.'+ T3.TABLE_NAME) and 
 P.minor_id = C.ORDINAL_POSITION
 for xml path('_ColumnExtProp'), type
 )
 from INFORMATION_SCHEMA.COLUMNS as C
 where C.TABLE_NAME = T3.TABLE_NAME and
 C.TABLE_SCHEMA = T2.TABLE_SCHEMA
 order by C.ORDINAL_POSITION
 for xml path('_Col'), type
 )
 from INFORMATION_SCHEMA.TABLES as T3
 where T3.TABLE_SCHEMA = T2.TABLE_SCHEMA and
 T3.TABLE_CATALOG = T1.TABLE_CATALOG
 order by T3.TABLE_NAME
 for xml path('_Table'), type
 )
 from INFORMATION_SCHEMA.TABLES as T2
 where T1.TABLE_CATALOG = T2.TABLE_CATALOG
 group by T2.TABLE_SCHEMA
 order by T2.TABLE_SCHEMA
 for xml path('_Schema'), type 
 )
from INFORMATION_SCHEMA.TABLES as T1
group by T1.TABLE_CATALOG
for xml path('_Catalog')

With this table in a database DBName.

CREATE TABLE [dbo].[TableName](
 [ID] [int] NOT NULL,
 [Name] [nchar](10) NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Column description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TableName', @level2type=N'COLUMN',@level2name=N'ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Table description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TableName'
GO

The output is.

<_Catalog TABLE_CATALOG="DBName">
 <_Schema TABLE_SCHEMA="dbo">
 <_Table TABLE_NAME="TableName">
 <_TableExtProp TableExtPropName="MS_Description" TableExtPropValue="Table description" />
 <_Col COLUMN_NAME="ID">
 <_ColumnExtProp ColumnExtPropName="MS_Description" ColumnExtPropValue="Column description" />
 </_Col>
 <_Col COLUMN_NAME="Name" />
 </_Table>
 </_Schema>
</_Catalog>

• EngineeringSQL: Mikael, your solution produces the desired result. Thank you.