Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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>
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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>
share|improve this answer
    
Mikael, your solution produces the desired result. Thank you. –  echoScout Aug 23 '12 at 13:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.