Melhores práticas para conectar bancos de dados que estão em diferentes regiões geográficas

Question

Dan

Asked: 2020-03-20 08:33:01 +0800 CST2020-03-20 08:33:01 +0800 CST 2020-03-20 08:33:01 +0800 CST

XQuery no SQL Server para converter dados de coluna XML em tabela de dados relacional

772

Anteriormente, fiz uma pergunta sobre um erro que estava recebendo. Você realmente não precisa disso para entender esta pergunta, mas está aqui para referência:

Erro XML/SQL Server 2008: XQuery... Não é possível atomizar ou aplicar implicitamente 'fn:data()' a elementos de conteúdo complexos

O xml anterior é um pouco complexo e provavelmente se beneficiaria de uma transformação, então apliquei um modelo XSLT para obter a estrutura abaixo e alterei um pouco as tags para que ficasse mais compreensível. Também reestruturei a tabela para a qual estou importando, para manutenção. Eu importei o arquivo XML transformado para uma tabela do SQL Server, xTable, with column xData, Assim (apenas uma linha, mas suponho que você possa importar mais de 1 e mesclá-los todos com a resposta de David Browne):

ID    xData
1     <MyXMLFile><Sample><Location>....

O nó pai do xml, <Sample>, pode ser repetido até 1 milhão de vezes, mas para esta ilustração, eu tenho apenas 2. Existem 22 nós filho para cada amostra, um <SampleID>nó e 21 <Location>nós (eu mostrei apenas 2 nós para manter coisas curtas). Existem 3 nós filhos para cada nó, um <LocationName>nó e dois <Foo>nós, designados <Foo1>e <Foo2>.

<?xml version="1.0" encoding="UTF-16"?>
<MyXMLFile>
    <!--There CAN BE up to 1 million <Sample> nodes-->
    <Sample>
        <!--There ARE EXACTLY 22 child nodes for each <Sample> parent node, one <SampleID> and 21 <Location>-->
        <SampleID>0000001A</SampleID>
        <!--There ARE EXACTLY 3 child nodes for each <Location> parent node, on <LocationID> and two <Foo>-->
        <Location>
            <LocationName>Jeff</LocationName>
            <Foo1>10</Foo1>
            <Foo2>11</Foo2>
        </Location>
        <Location>
            <LocationName>Jenn</LocationName>
            <Foo1>11</Foo1>
            <Foo2>12</Foo2>
        </Location>
    </Sample>
    <Sample>
        <SampleID>0000002A</SampleID>
        <Location>
            <LocationName>Greg</LocationName>
            <Foo1>13</Foo1>
            <Foo2>14</Foo2>
        </Location>
        <Location>
            <LocationName>Anne</LocationName>
            <Foo1>14</Foo1>
            <Foo2>16</Foo2>
        </Location>
    </Sample>
</MyXMLFile>

Eu quero converter a xDatacoluna xTablee colocá-la nesta tabela (coluna ID apenas para ilustração):

ID      SampleID    LocationName    Foo1   Foo2
1       00000001    Jeff            10     11     
2       00000001    Jenn            11     12     
…       00000001    …               …      …            
22      00000001    …               …      …     
23      00000002    Greg            13     14    
24      00000002    Anne            17     18
…       00000002    …               …      …
44      00000002    …               …      …

No momento, estou apenas tentando SELECTa xDatacoluna de xTablee editarei a consulta posteriormente para inserir os dados. Então, minha primeira consulta, apenas para mostrar que <SampleID>é selecionada:

Consulta 1

SELECT  a.b.query('SampleID').value('.', 'varchar(20)') AS SampleID

FROM xTable

CROSS APPLY xData.nodes('MyXMLFile/Sample') as a(b)

A saída parece boa:

ID      SampleID
1       00000001
2       00000002

Então, adicionei à consulta:

Consulta2

SELECT  a.b.query('SampleID').value('.', 'varchar(20)') AS SampleID,
        a.b.query('LocationName').value('.', 'varchar(10)') AS LocationName,
        a.b.query('Foo1').value('.', 'varchar(6)') AS Foo1,
        a.b.query('Foo2').value('.', 'varchar(6)') AS Foo2

FROM xTable
CROSS APPLY xData.nodes('MyXMLFile/Sample/SampleID/../Location') as a(b)

Para esta saída, nenhum dado é selecionado para <SampleID>. Isso não é surpreendente para mim, pois a seleção xpath é apenas para o <Location>nó pai e retorna seus filhos <LocationName>, <Foo1>e <Foo2>e não <SampleID>.

ID      SampleID    LocationName    Foo1   Foo2
1                   Jeff            10     11     
2                   Jenn            11     12     
…                   …               …      …            
22                  …               …      …     
23                  Greg            13     14    
24                  Anne            17     18     
…                   …               …      …
44                  …               …      …

Então eu tentei isso:

Consulta 3

SELECT  a.b.query('SampleID').value('.', 'varchar(20)') AS SampleID,
        c.d.query('LocationName').value('.', 'varchar(10)') AS LocationName,
        c.d.query('Foo1').value('.', 'varchar(6)') AS Foo1,
        c.d.query('Foo2').value('.', 'varchar(6)') AS Foo2
FROM xTable
CROSS APPLY xData.nodes('MyXMLFile/Sample/SampleID') as a(b)
CROSS APPLY xData.nodes('MyXMLFile/Sample/SampleID/../Location') as c(d)

A saída é um pouco melhor, mas as linhas são duplicadas na tabela. Deveria haver apenas 44, mas há 88:

ID      SampleID    LocationName    Foo1   Foo2
1       00000001    Jeff            10     11     
2       00000001    Jenn            11     12     
…       00000001    …               …      …            
42      00000001    …               …      …     
43      00000001    …               …      …
44      00000001    …               …      …
45      00000002    Greg            13     14    
46      00000002    Anne            17     18
…           …       …               …      …
88      00000002    …               …      …

Então pensei em tentar de uma maneira diferente.

Consulta 4

DECLARE @x xml;
SELECT @x = xData
FROM xTable
SELECT a.b.value('(SampleID/text())[1]', 'varchar(20)') AS SampleID,
       a.b.value('(LocationName/text())[1]', 'varchar(10)') AS LocationName,
       a.b.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1,
       a.b.value('(Foo2/text())[1]', 'varchar(6)') AS Foo2

FROM @x.nodes('MyXMLFile/Sample') AS xData(a)
CROSS APPLY @x.nodes('MyXMLFile/Sample/SampleID/../Location') AS a(b)

Agora, ao invés de SampleIDcampo em branco ou registros duplicados, SampleIDvoltou NULLe os dados foram duplicados:

ID      SampleID    LocationName    Foo1   Foo2
1       NULL        Jeff            10     11     
2       NULL        Jenn            11     12     
…       NULL        …               …      …            
42      NULL        …               …      … 
43      NULL        …               …      …
44      NULL        …               …      …
45      NULL        Greg            13     14    
46      NULL        Anne            17     18
…       NULL        …               …      …
88      NULL        …               …      …

Então, em uma tentativa final de selecionar os dados corretos, tentei esta consulta:

Consulta 5

DECLARE @x xml;
SELECT @x = xData
FROM xTable
SELECT a.b.value('(SampleID/text())[1]', 'varchar(20)') AS SampleID,
       c.d.value('(LocationName/text())[1]', 'varchar(10)') AS LocationName,
       c.d.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1,
       c.d.value('(Foo2/text())[1]', 'varchar(6)') AS Foo2

FROM @x.nodes('MyXMLFile/Sample') AS xData(a)
CROSS APPLY @x.nodes('MyXMLFile/Sample') AS a(b)
CROSS APPLY @x.nodes('MyXMLFile/Sample/SampleID/../Location') AS c(d)

O resultado aqui é ainda mais surpreendente para mim, não apenas a consulta preencheu todos os campos, mas quadruplicou a saída:

ID      SampleID    LocationName    Foo1   Foo2
1       00000001    Jeff            10     11     
2       00000001    Jenn            11     12     
…       00000001    …               …      …            
…       00000001    …               …      …     
…       00000001    …               …      …    
44      00000001    …               …      …
45      00000002    Greg            13     14   
46      00000002    Anne            17     18
47      00000002    …               …      …
48      00000002    …               …      …
…           …       …               …      …
176     00000002    …               …      …

Entendo que meu problema é a incorporação dos dois xpaths diferentes na consulta e meu entendimento e uso das tabelas derivadas na consulta. Qualquer ajuda seria apreciada. Como posso ajustar essas consultas para obter a tabela de que preciso?

Desde já, obrigado.

EDIT: Seguindo o conselho da resposta de David Browne, isso funciona para mim:

Consulta 6

INSERT INTO MyTable (SampleID, LocationName, Foo1, Foo2)
SELECT Sample.n.value('(SampleID)[1]', 'varchar(20)') AS SampleName,
       Location.n.value('(LocationName/text())[1]', 'varchar(1)') AS LocationName,
       Location.n.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1,
       Location.n.value('(Foo2/text())[1]', 'varchar(6)') As Foo2
FROM xTable AS x
CROSS APPLY x.xData.nodes('/MYXMLFile/Sample') AS Sample(n)
CROSS APPLY Sample.n.nodes('Location') AS Location(n)

1 respostas

Voted

David Browne - Microsoft · Answer 1 · 2020-03-20T11:52:17+08:00

O padrão é que cada um cross applypega a localização relativa do pai. Tente algo assim:

declare @doc xml =N'<?xml version="1.0" encoding="UTF-16"?>
<MyXMLFile>
    <!--There CAN BE up to 1 million <Sample> nodes-->
    <Sample>
        <!--There ARE EXACTLY 22 child nodes for each <Sample> parent node, one <SampleID> and 21 <Location>-->
        <SampleID>0000001A</SampleID>
        <!--There ARE EXACTLY 3 child nodes for each <Location> parent node, on <LocationID> and two <Foo>-->
        <Location>
            <LocationName>Jeff</LocationName>
            <Foo1>10</Foo1>
            <Foo2>11</Foo2>
        </Location>
        <Location>
            <LocationName>Jenn</LocationName>
            <Foo1>11</Foo1>
            <Foo2>12</Foo2>
        </Location>
    </Sample>
    <Sample>
        <SampleID>0000002A</SampleID>
        <Location>
            <LocationName>Greg</LocationName>
            <Foo1>13</Foo1>
            <Foo2>14</Foo2>
        </Location>
        <Location>
            <LocationName>Anne</LocationName>
            <Foo1>14</Foo1>
            <Foo2>16</Foo2>
        </Location>
    </Sample>
</MyXMLFile>'

drop table if exists #xData;

with q as
(
    select 1 ID, @doc xData
    union all 
    select 1 ID, @doc xData
)
select *
into #xData
from q

SELECT Sample.n.value('(SampleID)[1]', 'varchar(20)') AS SampleID,
       Location.n.value('(LocationName/text())[1]', 'varchar(10)') AS LocationName,
       Location.n.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1,
       Location.n.value('(Foo2/text())[1]', 'varchar(6)') AS Foo2

FROM #xData x
cross apply x.xData.nodes('/MyXMLFile/Sample') AS Sample(n)
cross apply Sample.n.nodes('Location') as Location(n)

saídas

SampleID             LocationName Foo1   Foo2
-------------------- ------------ ------ ------
0000001A             Jeff         10     11
0000001A             Jenn         11     12
0000002A             Greg         13     14
0000002A             Anne         14     16
0000001A             Jeff         10     11
0000001A             Jenn         11     12
0000002A             Greg         13     14
0000002A             Anne         14     16

(8 rows affected)

XQuery no SQL Server para converter dados de coluna XML em tabela de dados relacional

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

XQuery no SQL Server para converter dados de coluna XML em tabela de dados relacional

1 respostas

relate perguntas