Dan's questions

Tenho a seguinte tabela:

MyTable
PlaceID NameID  Code    CodeCount
1       1       1234A   2
1       2       1234A   3
1       3       1234A   1
2...
2...
3...
3...

Eu quero selecionar 1234Aonde PlaceID= 1, o número de vezes igual à soma do CodeCount. Então, o resultado seria seis linhas assim:

MyResult
Code    
1234A
1234A
1234A
1234A
1234A
1234A

Eventualmente, essa seleção terá vários códigos diferentes para um único arquivo PlaceID. SIM eu sei que estou duplicando dados, no final não vai ser muito e vou explicar porque estou fazendo isso abaixo.

Primeiro, eu precisava obter a soma das contagens:

DECLARE @cdcnt INT = (SELECT SUM(CodeCount) FROM MyTable WHERE PlaceID = 1 AND Code = '1234A')

Então eu posso usar esse valor como um controle em um loop while para selecionar o valor que muitas vezes:

DECLARE @i INT = 0;
WHILE @i < @cdcnt
BEGIN
    SELECT DISTINCT Code
    FROM MyTable
    WHERE PlaceID = 1 AND Code = '1234A'
    SET @i = @i + 1
END

O problema é que recebo 6 seleções separadas, em vez de uma seleção com 6 linhas. Como faço para criar MyResultmostrado acima, em vez de 6 seleções separadas?

Por que estou fazendo isso: quero usar um gerador de números aleatórios para escolher um Codecom a frequência em que ele aparece agregado para cada um PlaceID(haverá outros Codesadicionados, ainda estou descobrindo como fazer apenas um). Estou aberto a sugestões de diferentes maneiras de fazer isso, mas é a única maneira de descobrir como. Se houver uma função já construída para escolher de uma lista de acordo com frequências definidas, sou todo ouvidos.

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)

Tenho três tabelas:

Tabela 1

table1pode ter de 50 mil a 800 mil linhas exclusivas. Com isso, quero dizer que a combinação de todos os valores em cada linha é única, embora às vezes algumas colunas correspondam. É POSSÍVEL que uma linha inteira corresponda, exceto a NAMEcoluna, mas é muito, muito improvável. A coluna NAME será sempre exclusiva.

A NAMEcoluna é do tipo varchar(20). O restante das colunas para cada registro são todos do tipo varchar(6), onde cada duas colunas é um par de conjuntos e a tabela tem até 21 pares de conjuntos (ou seja, 43 colunas no total). Aqui está um exemplo table1mostrando um par definido para 4 registros (usei caracteres únicos aqui para facilitar, mas não esqueça que eles são type varchar(6)):

table1

NAME        pair1_1     pair1_2     ...up to pair21_1 - pair21_2
00001A      A           B       
00002A      A           A       
00003A      B           C       
00004A      A           B       
…up to 800k rows

Mesa 2

table2é configurado exatamente da mesma maneira, exceto que contém registros completamente diferentes do mesmo tipo exato (aqui pode haver de 1 a 200 linhas)

table2

NAME        pair1_1    pair1_2    ...up to pair21_1 - pair21_2
1234B       A          B
5678B       A          A
9101B       C          C
1213B       A          B        
…up to 200 rows

Tabela 3

table3está associado a uma única linha em table2e pode ser caracterizado como todo resultado possível de uma comparação entre essa única linha em table2qualquer linha possível que PODERIA ESTAR em table1. Provavelmente é melhor chamá-lo pelo NAMEin table2pertencente a ele (vamos usar apenas o primeiro e chamá-lo table1234Bporque se houver 200 linhas na tabela2, haverá 200 table3s associados diferentes).

Esta 3ª tabela conterá 4 linhas. Tem uma NAMEcoluna que é a varchar(20)e 21 conjuntos de 7 colunas (cada uma associada a um par diferente de table1e table2). As colunas adicionais res1_1são res1_2…res21_7type decimal(30,7).

Aqui está o que parece:

table1234B

NAME        res1_1  res1_2  res1_3  res1_4  res1_5  res1_6  res1_7  ….res21_7
1234B       12.30   1.000   0.2500  1.000   2.000   2.10    25.00   ….

Eu quero usar a combinação de dados compartilhados nos pares de colunas de table1e table2(ou seja, como eles combinam) como a condição para selecionar os dados table1234B(mostrarei um pouco disso abaixo). Apenas pares coincidentes serão comparados. pair1_1e pair1_2from table1será comparado com pair1_1e pair1_2de table2, o pair2s só será comparado com pair2s, pair3s apenas com pair3s etc. Portanto, não são feitas comparações de pares cruzados (por exemplo pair1, nunca serão comparados com pair2)

Neste exemplo abaixo, os campos pair1_1de cada tabela correspondem (A) e os campos pair1_2de cada tabela correspondem (B), mas os campos não correspondem entre as colunas de cada tabela.

NAME        Pair1_1     Pair1_2
00001A      A           B       (from table1)
1234B       A           B       (from table2)

Então eu quero SELECT dizer pair1_4de table1234Be associá-lo com a comparação entre os registros 00001A e 1234B.

Se as tabelas fossem assim, podemos ver que todos os 4 campos correspondem.

NAME        pair1_1     pair1_2
00001A      A           A       (from table1)
1234B       A           A       (from table2)

Neste caso, talvez eu queira escolher pair1_1entretable1234B

Aqui podemos ver que pair1_1em table1combina ambos os campos de table2mas pair1_2de table1não corresponde a nada.

NAME        pair1_1     pair1_2
00001A      A           B       (from table1)
1234B       A           A       (from table2)

Então eu quero escolher dizer pair1_4detable1234B

As opções acima são apenas 3 das 14 maneiras possíveis de compartilhar os dados nos pares de colunas entre as tabelas (há várias maneiras de não compartilhar dados entre as tabelas também), mas apenas 7 colunas possíveis para cada par selecionar table1234B.

Eu quero selecionar TODOS os valores table1234Bque atendem aos critérios estabelecidos de todos os possíveis compartilhamentos entre as colunas em cada conjunto de 2 de table1e table2. Será algo assim quando concluído:

1234BResult

NAME        RESULTPair1     …up to Resultpair21
00001A      12.30       (res1_1 from table3)        
00002B      1.000       (res1_2 from table3)                
00003C      25.00       (res1_7 from table3)        
00004A      1.000       (res1_4 from table3)
…up to 800K rows

Aqui está minha consulta editada com a qual estou começando.

SELECT t1.NAME as NAME, t3.pair1_4 as RESULTPair1
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON (t1.pair1_1 = t2.pair1_1 AND t1.pair1_1 <> t1.pair1_2 AND t1.pair1_2 = t2.pair1_2 AND t2.pair1_1 <> t2.pair1_2)
LEFT OUTER JOIN table1234B t3
ON t2.NAME = t3.NAME

… e o resultado dessa consulta

NAME        RESULTPair1 
00001A      1.000       (res1_4 from table3)
00002B      NULL
00003A      NULL
00004A      1.000       (res1_4 from table3)

Isso está mais próximo do que procuro, pois deixa os NAMEs de table2com diferentes compartilhamentos abertos a outros valores de table1234B. Pretendo que a lógica restante, uma vez incorporada, retorne os outros resultados apropriados.

O problema é que isso só me dá o resultado de qualquer situação que seja AB, AB. Eu preciso expandir isso para 21 pares e potencialmente 200 table3s, incluindo todos os diferentes resultados possíveis, table3se aplicável. Eu posso lidar com toda a lógica de compartilhamento (ou seja , pair1_1 = pairt2_2ou pair1_1 <> pair2_2) expandindo-a para as situações restantes e potencialmente mais registros dos table2quais estou perdido. Eu preciso obter resultados para todos os 800k registros table1para todos os 21 pares.

Se você ficou comigo tanto tempo e entendeu toda aquela coisinha de galinha, minhas perguntas são:

1. Como eu editaria minha consulta acima para trabalhar com um JOINem vez de um WHERE?
2. Haveria uma maneira de expandir isso para incluir eficientemente a lógica dos cenários adicionais possíveis de compartilhamento entre os campos table1e table2para que eu possa ver ou armazenar em uma nova tabela todos os resultados de 50 a 800k da table1linha única em table2comparação?
3. Como diabos posso expandi-lo para os 20 pares adicionais?

EDIT Depois de ser questionado sobre a conexão com a table3 percebi que a coluna precisava de uma edição para que isso funcionasse corretamente. Ainda estou tendo problemas para descobrir como fazer a consulta funcionar corretamente. Editei a consulta também. Ainda estou tendo um pouco de dificuldade para expandi-lo para várias colunas. Foi sugerido o uso de INTERSECT + EXCEPT ou NOT EXISTS. Não consegui que o INTERSECT retornasse nada, enquanto a consulta com a edição para incluir as junções à esquerda faz.

Atualização em 30/10/2018. Caso alguém esteja interessado, comecei a documentar no final deste post o que estou realmente fazendo para resolver isso, só porque encontrei algumas coisas interessantes.

Isenção de responsabilidade! Eu sou novo em muito disso. Eu tive uma exposição autodidata bastante limitada ao servidor SQL, VBA, XPATH, XSD, XML e um ano de treinamento formal em lógica (Java). Meu trabalho me migrou para uma posição única há mais ou menos um ano para me expor ao SQL e ao XML, então ainda sou um novato. As coisas que eu posto/pergunto podem ser muito ingênuas e se assim for, peço desculpas e aceito críticas francas, duras, sugestões sobre função e adequação da forma e boas referências de cópia impressa (não me refiro aos documentos da Microsoft porque raramente consigo descobrir cara ou coroa deles). Então vamos lá!

Estou recebendo esse erro com um projeto em que estou trabalhando e, embora existam alguns posts sobre isso aqui e em outros lugares, nada parece estar ajudando. Meu palpite é que estou tendo um problema de expressão XPATH... inicialmente.

XQuery [XMLTestTable.DATA.value()]: Não é possível atomizar ou aplicar implicitamente 'fn:data()' a elementos de conteúdo complexo, tipo encontrado 'xs:anyType' dentro do tipo inferido 'element({urn:MyFile-schema}:SUBUNITPRICE ,xs:qualquer tipo) *'.

Como você provavelmente pode adivinhar, este é um projeto de importação/conversão XML para tabela relacional. Eu encontrei bastante na net sobre esse tipo de coisa, mas muito pouca explicação sobre o que isso significa (embora eu tenha algumas suposições educadas na maior parte).

Eu vou começar aqui. Esquemas:

Algumas vezes as pessoas importam um esquema primeiro, às vezes não e apenas rolam com ele. Do jeito que eu entendo os esquemas, eles são para 1: validação do documento xml para algum tipo de padrão para garantir que tudo funcione/importa/exporta sem problemas 2: aumenta potencialmente a eficiência de importação/exportação/edição de um arquivo. Embora eu ache que validar o documento em si adiciona tempo à importação (suponho que sejam simplesmente etapas extras), as consultas downstream talvez sejam mais eficientes, embora eu não tenha chegado tão longe. De qualquer forma, eu pensei como uma coisa natural e prática que era uma boa ideia. Então aqui está o meu esquema (este foi transcrito à mão, então se você ver algo ruim, por favor, grite comigo!). Além disso, eu uso um nó chamado SUBUNITPRICE, por favor, entenda que isso não é realmente uma unidade monetária e eu' Alteramos alguns nomes de nós para manter as coisas um pouco mais confidenciais. Apenas saiba que esse valor de nó é um valor de texto que pode conter números e símbolos.

<?xml version="1.0" encoding="UTF-8"?>
 <xs:schema  xmlns:xs= "http://www.w3.org/2001/XMLSchema" targetNamespace="urn:MyFile-schema" xmlns="urn:MyFile-schema" elementFormDefault="qualified">
         <xs:element name="MyFile">
               <xs:complexType>
                     <xs:sequence>
                           <xs:element name="FIELD1" type="xs:double"/>
                           <xs:element name="FIELD2" type="xs:string"/>
                           <xs:element name="FIELD3" type="xs:string"/>
                           <xs:element name="FIELD4" type="xs:string"/>
                           <xs:element name="FIELD5" type="xs:string"/>
                           <xs:element name="FIELD6" type="xs:dateTime"/>
                           <xs:element name="GROUP" maxOccurs="unbounded">
                                 <xs:complexType>
                                       <xs:sequence>
                                             <xs:element name="GROUPID" type="xs:string"/>
                                             <xs:element name="GROUPCATEGORY" type="xs:string"/>
                                             <xs:element name="UNIT" maxOccurs="unbounded">
                                                   <xs:complexType>
                                                         <xs:sequence>
                                                               <xs:element name=" UNITNAME" type="xs:string"/>
                                                               <xs:element name="REVIEWER" type="xs:string"/>
                                                               <xs:element name="DATEANDTIME" type="xs:dateTime"/>
                                                               <xs:element name="SUBUNIT" maxOccurs="unbounded">
                                                                     <xs:complexType>
                                                                           <xs:sequence>
                                                                                 <xs:element name=" SUBUNITPRICE" type="xs:string"/>
                                                                           </xs:sequence>
                                                                     </xs:complexType>
                                                               </xs:element>
                                                         </xs:sequence>
                                                         <xs:attribute name="MULTIENTRY" type="xs:string"/>
                                                         <xs:attribute name="PARTIALUNIT" type="xs:string"/>
                                                         <xs:attribute name="KIT" type="xs:string"/>
                                                   </xs:complexType>
                                             </xs:element>
                                       </xs:sequence>
                                       <xs:attribute name="FULL" type="xs:string"/>
                                       <xs:attribute name="VER" type="xs:string"/>
                                 </xs:complexType>
                           </xs:element>
                     </xs:sequence>
                     <xs:attribute name="MyFile" type="xs:string"/>
               </xs:complexType>
         </xs:element>
   </xs:schema>

Meu XML. Os comentários estão nele após os nós GROUP, UNITNAME e SUBUNIT para referências ao comprimento do nó, estrutura e tamanho potencial do arquivo XML (potencialmente 100k nós GROUP para um arquivo de cerca de 500-600 MB cada). Reticências (...) basta marcar que existe uma iteração daquele nó específico e o número de iterações que espero que encontre implícito nos valores do nó. Pode ser que vários desses arquivos precisem ser importados para um banco de dados do SQL Server a qualquer momento.

<?xml version="1.0" encoding="utf-8"?>
<MyFile xmlns="urn:MyFile-schema">
    <FIELD1>FileName</ FIELD1>
    <FIELD2>Foo</ FIELD2>
    <FIELD3>Bar</ FIELD3>
    <FIELD4>Upload</ FIELD4>
    <FIELD5>UserName</ FIELD5>
    <FIELD6>UploadTime</ FIELD6>
    <GROUP VER="Yes" FULL="false">     ‘<---- There can be up to 100K of these (maybe more soon) ---identified by child node GROUPID
        <GROUPID>GrName1</ GROUPID>   
        <GROUPCATEGORY>MyCategory</ GROUPCATEGORY>
        <UNIT KIT=”1” PARTIALUNIT ="false" MULTIENTRY=”Yes”> ‘<---- 12 to 35 possible in each GROUP NODE ---identified by child UNITNAME
            <UNITNAME>Unit1</ UNITNAME>                 
            <REVIEWER>UserName</ REVIEWER>
            <DATEANDTIME>DateTime</ DATEANDTIME>
            <SUBUNIT> ‘<---- 2 in each UNIT NODE most of time – will be same tag SUBUNIT
                <SUBUNITPRICE>11.50</ SUBUNITPRICE>
            </ SUBUNIT>
            < SUBUNIT> ‘<-sometimes 3 in intermittent unit nodes but rarely included in file
                < SUBUNITPRICE >20.00</ SUBUNITPRICE>
            </ SUBUNIT>
        </ UNIT>
        ...
        <UNIT KIT=”1” PARTIALUNIT ="false" MULTIENTRY=”Yes”>
            <UNITNAME>Unit23</ UNITNAME> 
            <REVIEWER>UserName</ REVIEWER>
            <DATEANDTIME>DateTime</ DATEANDTIME>
            <SUBUNIT> 
                <SUBUNITPRICE>$11.50</ SUBUNITPRICE>
            </ SUBUNIT>
            < SUBUNIT> 
                <SUBUNITPRICE>$20.00</ SUBUNITPRICE>
            </ SUBUNIT>
        </ UNIT>
    </GROUP>
    <GROUP VER="Yes" FULL="false">
        <GROUPID>GroupName100,000</ GROUPID>   
        ...
    </GROUP>
</MyFile>

Aqui está o que eu tenho feito com isso.

Importação de esquema:

--Import the schema
IF EXISTS(SELECT * FROM sys.xml_schema_collections where [name] = 'XMLSchema')
DROP XML SCHEMA COLLECTION[XMLSchema]

DECLARE @MySchema XML
SET @MySchema = 
(SELECT * FROM OPENROWSET
    (BULK 'C:\Path\Schema.xsd', SINGLE_BLOB) AS xmlData
)
CREATE XML SCHEMA COLLECTION[XMLSchema] AS @MySchema

Carregue o arquivo. Isso leva cerca de 2 minutos para um arquivo GROUP Node de 50k, não tenho ideia de quanto tempo levará para 100k. Eu gostaria de acelerar isso. Eu adiciono um índice à coluna xml. Não tenho certeza sobre essa parte, exceto que a peguei como um conselho de que acelera um pouco as coisas com consultas a jusante e sei que funciona. Eu sei que eu poderia economizar algum tempo e recursos editando os atributos desnecessários (todos eles) e campos, mas descobri que isso apenas reduz o tempo no front-end fazendo o trabalho de removê-los.

Eu entendo que se eu adicionar índices secundários à coluna XML, isso pode realmente acelerar as coisas. Não faço ideia por onde começar com isso. Se alguém tiver algumas sugestões para uma referência, ou uma adição rápida, eu apreciaria a entrada.

CREATE TABLE XMLTestTable
( 
ID INT IDENTITY PRIMARY KEY,
DATA xml(CONTENT MyXmlSchema)
)

INSERT INTO XMLTestTable
(DATA)

SELECT CONVERT(XML, BulkColumn) as BulkColumn


--import an xml file into the column
FROM OPENROWSET(BULK 'C:\Path\FileName.XML', SINGLE_BLOB) as x

CREATE PRIMARY XML INDEX PXML_DATA 
ON XMLTestTable (DATA)

Em seguida: Eu preciso colocar o GROUPID e SUBUNITPRICEs para cada GROUPID em uma tabela, vou chamá-lo de GROUPTABLE e espero que fique assim:

|ID    |GROUPID|UNIT1_SUBUNITPRICE_1|UNIT1_SUBUNITPRICE_2|……|UNIT23_SUBUNITPRICE_2|
|1     |GrName1|11.50               |20.00               |……|25.00                |
|2     |GrName2|1.00                |32.41               |……|45.51                |

Então eu crio as tabelas:

CREATE TABLE GROUPTABLE
(
ID int IDENTITY(1,1) PRIMARY KEY,
GROUPID varchar(20),
UNIT1_SUBUNITPRICE_1 varchar(7),
UNIT1_SUBUNITPRICE_2 varchar(7),
…
UNIT23_SUBUNITPRICE_1 varchar(7),
UNIT23_SUBUNITPRICE_2 varchar(7)
)

Agora a parte que gera o erro! Para fins de demonstração, suponha que eu adicionei apenas os campos ID e GROUPID à tabela aqui. Se eu começar apenas tentando inserir o índice e o groupid, funciona muito bem! Assim:

–-migrate the data from the xmlcolumn to the table
WITH XMLNAMESPACES(DEFAULT 'urn:MyFile-schema')

INSERT INTO GROUPTABLE
Select
    t.b.value('GROUPID[1]', 'varchar(20)') AS GROUPID 
    FROM XMLTestTable 
CROSS APPLY
    DATA.nodes('//MyFile/GROUP) AS t(b)

...e então o problema. Aqui, suponha que eu adicionei apenas o ID e UNIT1_SUBUNITPRICE_1 à tabela. Vamos tentar adicionar os subunitprices:

–-migrate the data from the xmlcolumn to the table
WITH XMLNAMESPACES(DEFAULT 'urn:MyFile-schema')

INSERT INTO GROUPTABLE

Select 
    t.b.value('UNIT[UNITNAME=“Unit1”]/../SUBUNIT[1]/SUBUNITPRICE[1]', 'varchar(7)') AS SUBUNITPRICE_1
FROM XMLTestTable 
CROSS APPLY
    DATA.nodes('//MyFile/GROUP) AS t(b)

Gerando o erro que mencionei no início.

XQuery [XMLTestTable.DATA.value()]: Não é possível atomizar ou aplicar implicitamente 'fn:data()' a elementos de conteúdo complexo, tipo encontrado 'xs:anyType' dentro do tipo inferido 'element({urn:MyFile-schema}:SUBUNITPRICE ,xs:qualquer tipo) *'.

Se você ficou comigo por essa longa coisa, eu agradeço! Se você chegou até aqui, aqui estão minhas perguntas:

1. O que estou fazendo de errado com essa expressão xpath na consulta?
2. O que posso fazer para acelerar a importação do arquivo XML para a coluna?
3. O que posso fazer para acelerar a migração da coluna para a tabela?
4. Seria melhor fazer um monte de tabelas para cada UNIDADE em vez de 1 tabela para todas elas?
5. Por último, ouvi rumores de pessoas pegando um objeto XML e analisando o arquivo dizendo 10k linhas de cada vez e fazendo um loop por elas em um procedimento como este (importar parte do arquivo como uma variável de objeto, colocá-lo na tabela, repetir em vários linhas e, em seguida, migrar em 10 mil linhas de cada vez também). Isso é possível e ajudaria?

Atualização 30/10/2018

Quanto à importação de arquivos xml, tentei várias coisas.

1. Eu brinquei usando uma variável xml em vez de despejá-la em uma tabela e descobri que a variável carrega um pouco mais rápido... economiza talvez um ou dois segundos no carregamento do xml. Também comparei o carregamento direto na tabela versus o carregamento do xml em uma variável e, em seguida, inserindo a variável na coluna. Eles levaram a mesma quantidade de tempo, ou foi pelo menos insignificante. Ainda tenho que comparar se a consulta da variável ou da coluna é mais rápida e se a indexação tem ou não impacto, embora tudo o que li indique que a indexação (principalmente índices secundários com mais xmls aninhados) diminui muito o tempo de consulta pós-carregamento. No momento, meus testes nesta atualização não envolveram uma variável. Não tenho certeza para onde ir a partir daqui, mas tenho algumas idéias e atualizarei se e quando o fizer.

2. Não validar para um esquema é mais rápido. Se o arquivo já está validado, ou você pode confiar nos dados, isso não vale a pena. Eu removi isso de testes adicionais por enquanto.

3. A remoção da chave primária na coluna xml permite um carregamento muito mais rápido em uma coluna. Como há muita sobrecarga no armazenamento dos dados como um xml e meu objetivo é convertê-lo para o formulário RDBMS, isso provavelmente também não vale a pena, mas ainda não tenho certeza. Por enquanto, eu removi isso de outros testes também.

4. Eu mexi no carregamento de vários arquivos de números menores do nó pai GROUP e isso fez uma grande diferença no que diz respeito ao carregamento do xml (ainda tenho que ver como isso afeta o tempo de consulta e conversão). Criei conjuntos de xmls em quantidades incrementais de até 10.000 do nó GROUP e depois 50.000 do nó GROUP, depois escrevi uma consulta sql dinâmica para importá-los em massa (vou postar a consulta após os resultados que encontrei). A consulta coloca o xml diretamente em uma coluna table/xml.

O primeiro teste que fiz foi comparar uma carga direta em uma tabela, um único arquivo xml de 10.000 nós GROUP para carregar 10.000 arquivos com um nó GROUP cada.

1 arquivo de 10.000 nós GROUP levou 2 segundos para carregar em 1 linha

10.000 arquivos de 1 nó GROUP levaram 19 segundos para carregar em 10.000 linhas

O segundo teste que fiz foi comparar quantidades variadas de 50.000 nós GROUP.

10 arquivos de 5.000 nós GROUP levaram 13 segundos para carregar em 10 linhas

5 arquivos de 10.000 nós GROUP levaram 6 segundos para carregar em 5 linhas

1 arquivo de 50.000 nós GROUP levou 12 segundos para carregar em 1 linha

Claramente, 10k de cada vez é muito melhor do que qualquer outra coisa aqui, pelo menos para 50K GROUP Nodes. Talvez isso mudasse se eu estivesse carregando, digamos, 100 mil nós. Talvez eu tente isso amanhã e dou um retorno.

NOTA: Eu usei o cronômetro de consulta no canto inferior direito, então a precisão aqui é de 1 segundo.

Aqui está a consulta sql dinâmica que usei para fazer isso:

--variables for dynsql and loop
DECLARE @i int --I named the files with an integer so I could insert them into the loop
DECLARE @dsql varchar(Max)

--create xml table
CREATE TABLE xTable
(
ID INT IDENTITY PRIMARY KEY,
xData XML NOT NULL
);

--loop dynsql to import xml to xTable
Set @i = '1'
WHILE @i <=1 --I changed this depending on how many files I was loading
BEGIN
Set @dsql = 'INSERT INTO xTable(xData)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(Bulk ' + Char(39) + 'C:\MyPath\' + CAST(@i as nvarchar(5)) + '.xml' + CHAR(39) + ', SINGLE_BLOB) x;'
Exec(@dsql)
SET @i = @i + 1
END;

Isso me traz mais perguntas para as quais não estou procurando respostas aqui, mas as escrevo para que qualquer um que siga pondere.

1. Qual será a diferença na conversão de xml para RDBMS entre 1 linha de 50k GROUP Nodes e 5 linhas de 10k GROUP Nodes?

2. Converter 1 xml de 50k GROUP Nodes em 5 xmls de 10k GROUP Nodes demora um pouco (eu fiz isso com o Excel VBA ... eu sei que poderia usar outra coisa, mas é o que eu tenho e sei). Acho que a maneira mais criteriosa de fazer isso provavelmente fará algum trabalho XSLT (isso é algo que preciso aprender)

3. Ao fazer isso, me fez pensar se havia uma maneira mais rápida de analisar o xml em pedaços de nó de 10k por meio da variável sql server xml no sql server e seria mais rápido do que fazê-lo fora do sql server (não sei onde comece aqui).

Para quem ainda segue ou talvez se você aconteceu neste tópico

100 arquivos com 1.000 nós GROUP cada - 25 segundos

40 arquivos com 2.500 nós GROUP cada - 24 segundos

20 arquivos com 5.000 nós GROUP cada - 24 segundos

10 arquivos com 10.000 nós GROUP cada - 24 segundos

4 arquivos com 25.000 nós GROUP cada - 20 segundos

2 arquivos com 50.000 nós GROUP cada - 22 segundos

1 arquivo com 100.000 nós GROUP - 22 segundos