Início / dba / Perguntas / 54710
Accepted
Kahn
Asked: 2013-12-10 06:35:17 +0800 CST

Importar dados XML ao criar IDs com relacionamentos

  • 772

Preciso analisar o XML de um arquivo em tabelas enquanto retenho seus relacionamentos por meio de valores de ID gerados de forma personalizada.

Por exemplo, se eu tivesse o seguinte XML:

<root>
   <construction>
      <constructionName>randomname1</constructionName>
      <project>
         <projectname>another randomname</projectname>
         <businesspartners>
            <partnername>bilbo bagginses</partnername>
         </businesspartners>
         <employees>
            <employee>
               <empname>frodo</empname>
               <empaddress>etc...</empaddress>
            </employee>
         </employees>
      </project>
      <info>
         <randElement></randElement>
      </info>
      <constructionType>houses</constructiontype>
   </construction>
   <construction>
      <...(etc, same as above, times n^10)/>
   </construction>
</root>

A partir disso, preciso gerar dados para as seguintes tabelas:

CONSTRUCTION (CONSTRUCTION_ID INT PRIMARY KEY
   , CONSTRUCTIONNAME VARCHAR..
   , CONSTRUCTIONTYPE VARCHAR.. )

PROJECT (PROJECT_ID INT PRIMARY KEY
   , CONSTRUCTION_ID INT FOREIGN KEY REFERENCES CONSTRUCTION
   , PROJECTNAME VARCHAR.., )


BUSINESSPARTNERS (BUSINESSPARTNERS_ID INT PRIMARY KEY
   , PROJECT_ID INT FOREIGN KEY REFERENCES PROJECT
   , PARTNERNAME VARCHAR..)

etc...

Basicamente, a ideia é construir tabelas com suas referências intactas para representar o XML. As estruturas da tabela já existem e não podem ser alteradas para acomodar este script. Isso é simplesmente uma questão de ser capaz de fazer o mesmo tipo de análise XML e, em seguida, adicionar os dados às tabelas enquanto gera os valores de ID de referência corretos, como fizemos antes com a integração. Só fazendo isso exclusivamente com SQL Server, e sem usar SSIS .

Agora, o cenário real e o arquivo em questão são bastante grandes, então não espero de forma alguma uma resposta completa. Apenas dicas sobre onde começar a procurar. Eu sou bastante inexperiente em lidar com XML.

Por enquanto, minha solução preferida seria apenas importar os dados com OPENROWSETe, em seguida, usar o SQL dinâmico OPENXMLpara analisar o documento nas tabelas, basicamente fazendo um loop dos elementos e seus subelementos, um de cada vez. Mas isso parece muito mais problemático do que alguma outra maneira mais inteligente.

Como os valores de ID são gerados?

Isso é parte da questão. Por enquanto não são. A ideia é que a cada CONSTRUCTIONelemento seria atribuído um IDaumento de 1. Então, todos os filhos de CONSTRUCTIONse refeririam ao mesmo IDatribuído ao pai e assim por diante. Basicamente, trata-se apenas de dividir os dados do XML em várias tabelas, mantendo intacta a integridade referencial.

Eu não teria nenhum problema em usar uma coluna de identidade como chave primária, desde que as relações não se misturassem como resultado. Não sei como fazer isso, então presumi que seria necessário, de alguma forma, definir manualmente a lógica para criar os IDs, em oposição à identidade.

sql-server import

1 respostas

  1. Best Answer

    Você pode usar uma variante da técnica descrita em Usando merge..output para obter o mapeamento entre source.id e target.id e por Adam Machanic em Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE .

    Você usa mergee captura o ID gerado com o fragmento XML que pertence a esse ID em uma variável de tabela e, em seguida, usa essa variável de tabela ao adicionar linhas às tabelas filhas.

    declare @C table
(
  CONSTRUCTION_ID int primary key,
  PROJECT xml
);

merge CONSTRUCTION as T
using (
      select T.X.value('(constructionName/text())[1]', 'varchar(30)') as CONSTRUCTIONNAME,
             T.X.value('(constructionType/text())[1]', 'varchar(30)') as CONSTRUCTIONTYPE,
             T.X.query('project') as PROJECT
      from @xml.nodes('/root/construction') as T(X)
      ) as S
on 0 = 1
when not matched by target then
  insert (CONSTRUCTIONNAME, CONSTRUCTIONTYPE) 
  values (S.CONSTRUCTIONNAME, S.CONSTRUCTIONTYPE)
output inserted.CONSTRUCTION_ID, S.PROJECT into @C;

declare @P table
(
  PROJECT_ID int primary key,
  BUSINESSPARTNERS XML
);

merge PROJECT as T
using (
      select C.CONSTRUCTION_ID,
             T.X.value('(projectname/text())[1]', 'varchar(30)') as PROJECTNAME,
             T.X.query('businesspartners') as BUSINESSPARTNERS
      from @C as C
        cross apply C.PROJECT.nodes('/project') as T(X)
      ) as S
on 0 = 1
when not matched by target then
  insert (CONSTRUCTION_ID, PROJECTNAME)
  values(S.CONSTRUCTION_ID, S.PROJECTNAME)
output inserted.PROJECT_ID, S.BUSINESSPARTNERS into @P;

insert into BUSINESSPARTNERS(PROJECT_ID, PARTNERNAME)
select P.PROJECT_ID,
       T.X.value('text()[1]', 'varchar(30)')
from @P as P
  cross apply P.BUSINESSPARTNERS.nodes('/businesspartners/partnername') as T(X);

    SQL Fiddle

    • 5

relate perguntas