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>