Início / dba / Perguntas / 22924
Accepted
EngineeringSQL
Asked: 2012-08-23 07:54:35 +0800 CST

Como faço para mesclar duas variáveis ​​XML no SQL Server (2005/2008)?

  • 772

Encaminhar: já li este tópico do StackOverflow e ele não aborda a retenção de profundidade de que preciso.

Desejo criar um documento XML que contenha os objetos de definição de dados para um único banco de dados, usando a hierarquia:

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

Eu tenho duas consultas construídas.

  • Um cria a hierarquia para o nível TABLE com elementos filhos TableExtendedProperty
  • Um cria a hierarquia para o nível COLUMN com elementos filhos ColumnExtendedProperty

O resultado XML (abreviado) para cada consulta é fornecido abaixo.

Preciso (a) mesclar essas duas variáveis ​​XML em uma única variável XML ou (b) construir uma consulta diferente que produza o resultado desejado.

O que você pode recomendar?

Código fonte

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>
sql-server xml

1 respostas

  1. Best Answer

    Você pode usar for xml pathem vez de for xml raw. Crie sua hierarquia em subconsultas correlacionadas na lista de campos. Você também pode usar order bynas subconsultas para ter as tabelas ordenadas alfabeticamente e as colunas ordenadas por ORDINAL_POSITION.

    Eu tentei traduzir o que você tem e cheguei a isso.

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

    Com esta tabela em um banco de dados 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

    A saída é.

    <_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>
    • 2

relate perguntas