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:
- O que estou fazendo de errado com essa expressão xpath na consulta?
- O que posso fazer para acelerar a importação do arquivo XML para a coluna?
- O que posso fazer para acelerar a migração da coluna para a tabela?
- Seria melhor fazer um monte de tabelas para cada UNIDADE em vez de 1 tabela para todas elas?
- 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.
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.
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.
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.
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.
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?
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)
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
Você deve colocar o
UNITNAMEelemento no predicado(UNIT[UNITNAME = "Unit1"]/SUBUNIT/SUBUNITPRICE)[1]Uma consulta de trabalho seria algo assim, onde
@Xtem todo o XML:Nada até onde eu sei. No entanto, eu iria carregá-lo para uma variável em vez de uma tabela.
If you don't need to validate the XML you could skip the schema. It takes time to validate. Some says that using a schema will speed up shredding the XML and that would be true if you tried the query above with and without schema. If you don't use a schema you need to rewrite the query to specify the
text()node and then you have the same shredding performance without the schema as without.It looks to me like you will eventually end up with a query that returns almost 50 columns. In the current implementation of querying the XML datatype in SQL Server that will be slow. More info here and vote for a change here.
The alternative to use OPENXML instead would look like this for you.
Note: You should not use the
text()node in OPENXML, it will be slower.Não estou familiarizado com a implementação específica de XQuery que você está usando, ou mesmo com a mistura de XQuery e SQL em geral, mas vou oferecer algumas sugestões de uma posição de considerável ignorância.
Em primeiro lugar, sua expressão de caminho
UNIT[UNITNAME=“Unit1”]/../SUBUNIT[1]/SUBUNITPRICE[1]parece errada.SUBUNITem seus dados é um filho deUNIT, então a..etapa que move para o pai doUNITelemento está errada. Normalmente, eu esperaria que um caminho incorreto como esse levasse à expressão de caminho não selecionando nada; no seu caso, acho que pode ter levado o processador a fazer uma inferência de tipo incorreta queSUBUNITPRICEé de tipoxs:anyTypee, portanto, não atomizável.Essa é a pergunta 1. Não sei a resposta para suas outras perguntas.