Início / dba / 问题

Perguntas[xml](dba)

Martin Hope
Fatdrfrog
Asked: 2021-11-23 03:35:06 +0800 CST
  • 0

Pela seguinte pergunta: Veja se o elemento XML existe em algum nível no documento com um valor específico

Existe uma solução possível para implementar a likecláusula dentro exist?

Por exemplo, eu tenho uma estrutura semelhante de XML como em questão, mas em "tag" : <ContactLName>somevalues;Brandt;anothervalues</ContactLName> "Brandt" armazenado um pouco diferente. Para encontrar "Btandt" tenho que usar algo como like, mas não encontrei nada.
Aqui está um exemplo do que estou procurando:
WHERE tmp.[XmlCol].exist(N'/*/ContactLName[text() like "%Brandt%"]') = 1;

Editar(XML): 
<event>
<id>23456</id>
<status_in_knowledge_map>active</status_in_knowledge_map>
<custom_elems>
    <custom_elem>
        <name>f_n84u</name>
        <value>6769747818850362088;6622044220776915827;5370802024842340642;6583457400600680603;6724970265512462551;6954452686001110038;6667448592984922570;6390613356250088198;6985638718331227440;5370802050233479119;5370802024842340639;6208275408034531857;6078654416541145539;6585528811993904287;6805915353243667957;6842010739999728536;6964839747571514100;6491764192266708067;6630060828301937234;6964682936739957053;6996382008379854236;5703859111941721724;6780468964682252783;</value>
    </custom_elem>
    <custom_elem>
        <name>f_twxr</name>
        <value>7031439420534698908;5667697629116916565;5667697629116916521;7032219545122506576;7032219609944822365;7032219012824308287;7032219395780000352</value>
    </custom_elem>
</custom_elems>
<asd>adsf</asd>
sql-server xml
Martin Hope
uberbloke
Asked: 2021-08-28 02:39:37 +0800 CST
  • 7

O seguinte (nos meus sistemas, testado no SQL Server 2016 e 2019 std) produz uma solicitação de concessão de memória massiva (3327 MB)

declare @wibble xml = '<wibble><wobble>1</wobble></wibble>'

select @wibble.query('//wobble[1]')

Existe alguma coisa que eu possa fazer para reduzir o subsídio?

https://www.brentozar.com/pastetheplan/?id=HkezxS8-K

sql-server xml
Martin Hope
FlanMan
Asked: 2020-11-15 06:40:04 +0800 CST
  • 6

Neste exemplo do SQL Server OPENXML:

declare @xmldata xml    

set @xmldata = 
'<?xml version="1.0"?>
<wddxPacket version="1.0">
    <data>
        <var dimension="task"><string>Task 1</string></var>
        <var dimension="startdate"><string>2020-11-14</string></var>
    </data>
    <data>
        <var dimension="startdate"><string>2020-11-15</string></var>
        <var dimension="task"><string>Task 2</string></var>
    </data>
</wddxPacket>'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT *
FROM OPENXML(@hDoc, '//wddxPacket/data',2)
        WITH(
            task          varchar(200) 'var[1]/string',
            startdate     varchar(200) 'var[2]/string'
    )

EXEC sp_xml_removedocument @hDoc

Esta é a saída atual:

task         startdate
------       ----------
Task 1       2020-11-14
2020-11-15   Task 2

Mas eu realmente quero essa saída:

task         startdate
------       ----------
Task 1       2020-11-14
Task 2       2020-11-15

Para identificar o conteúdo, não posso usar "var" sozinho, pois todos os elementos de interesse são denominados "var". Não posso usar a posição [1] ou [2], porque os números de posição são inconsistentes no XML, resultando na saída atual misturada. Como identifico os elementos com base nos nomes dos atributos "var" e "dimension" de "task" ou "startdate"?

sql-server xml
Martin Hope
user1664043
Asked: 2020-07-22 13:20:08 +0800 CST
  • 3

Estou recebendo esses arquivos xml de um fornecedor, e é um wrapper do esquema NITF (notícias) e o esquema de metadados http://www.xmlnews.org/namespaces/meta# news (do Space 1999!)

Infelizmente, eles não declaram nenhum namespace no documento externo. Isto é o que eles nos dão:

<?xml version="1.0"?>
<document>
<nitf>
    <head>...</head>
    <body>...</body>
    etc
</nitf>
<xn:Resource xmlns:xn="http://www.xmlnews.org/namespaces/meta#">...</xn:Resource>
</document>

Eu estava tentando ver se poderia melhorar a taxa de transferência criando uma coleção de esquema xml e analisando-a digitada, mas a falta de qualquer declaração de namespace no texto xml está me atrapalhando.

ja tentei colocar

;WITH XMLNAMESPACES (default 'http://iptc.org/std/NITF/2006-10-18/')
SELECT CAST(rawXml as XML(NitfSchemaCollection))

mas não gosta (Validação XML: Declaração não encontrada para exceção do elemento 'documento').

Eu até tentei usar ;WITH XMLNAMESPACES para obter o xml bruto analisado em um tipo XML e depois convertê-lo em XML (NitfSchemaCollection), mas o mesmo problema.

Então minhas perguntas são:

  1. além de reescrever os documentos xml recebidos do fornecedor, existe alguma maneira de obter a coleção de esquema xml aplicada à análise?

e

  1. A análise de tipos produziria aprimoramento de desempenho suficiente para levá-lo adiante?

No momento, estamos no Sql Server 2008 sp4, mas eu poderia tentar em uma instância mais recente se isso pudesse mudar alguma coisa.

EDIT: Aqui está um documento de exemplo. Ambos os nós nitf e xn:Resource estão em conformidade com dois padrões de serialização de serviço de newswire muito antigos. Para minha coleção de esquema, adicionei ambos e ajustei o nitf para adicionar o nó do documento , que não é padrão. O esquema é longo para um post, mas posso adicioná-los se alguém estiver interessado.

<?xml version="1.0"?>
<document>
<nitf>
<head>
    <title>First World Problems: 'Should I cancel my Easter holiday and charter a superyacht to escape coronavirus?'</title>
</head>
<body>
    <body.head>
        <hedline>
        <hl1>First World Problems: 'Should I cancel my Easter holiday and charter a superyacht to escape coronavirus?'</hl1>
        </hedline>
        <byline>
        <bytag>By Caroline White</bytag>
        </byline>
        <distributor>Telegraph Group</distributor>
    </body.head>
    <body.content>
    <p><em>'I am thinking of cancelling my Easter holiday and chartering a yacht to whisk my immediate family off to sea. The idea is that we can still enjoy the trip of a lifetime without risking contracting the coronavirus. How would you recommend proceeding?'</em></p>
    <p>If you’ve got the wallet for it, a superyacht charter offers the most luxurious seclusion on the planet – and like the hand sanitiser aisle in Boots, you’re not the first to think of it. Some brokers anticipate an uptick in superyacht sales, as UHNWI look to create safe havens, and wealthy holidaymakers are likely to follow suit. So get moving.</p>
    <p>The first step is to recruit a charter broker – try Fraser, Burgess, YPI or <org value="ACORN:3601037911" idsrc="xmltag.org" >Camper &amp; Nicholsons</org>. They will gauge your budget, preferences and read your personality (are you too formal for that laid-back Aussie captain; are you too wild for that silver-service English crew) then come back to you with a bespoke selection of options. The next step is a rather blissful journey through yacht brochures. Then there are the itineraries to flick through: beach barbeques, diving days and suppers under the stars…</p>
    ...blah blah blah...
    <p><em><em>If you have a question for any of our Telegraph Luxury experts, on any topic, please email <a href="http://mailto:[email protected]/">[email protected]</a></em></em></p>
    <p><em>Last week on First World Problems</em></p>
    <p><a href="https://www.telegraph.co.uk/luxury/womens-style/first-world-problems-expensive-blonde-highlights-mayfair-salon/">First World Problems: 'Are expensive highlights at a Mayfair salon worth the price-and the journey?'</a></p>
    <p><em><em>Sign up for the <a href="https://www.telegraph.co.uk/newsletters/Luxury/">Telegraph Luxury newsletter</a> for your weekly dose of exquisite taste and expert opinion.</em></em></p>
</body.content>
</body>
</nitf>
<xn:Resource xmlns:xn="http://www.xmlnews.org/namespaces/meta#">
    <xn:providerName>Telegraph Group</xn:providerName>
    <xn:providerCode>127</xn:providerCode>
    <xn:serviceName>Telegraph Online</xn:serviceName>
    <xn:serviceCode>2</xn:serviceCode>
    <xn:resourceID>202003100715TELEGR__ONLINE___60979152</xn:resourceID>
    <xn:publicationTime>2020-03-10T07:15:00-04:00</xn:publicationTime>
    <xn:receivedTime>2020-03-10T07:50:43-04:00</xn:receivedTime>
    <xn:title>First World Problems: 'Should I cancel my Easter holiday and charter a superyacht to escape coronavirus?'</xn:title>
    <xn:rendition>202003100715TELEGR__ONLINE___60979152.xml</xn:rendition>
    <xn:vendorData>WAVO:Publish Reason=CORRECTED</xn:vendorData>
    <xn:vendorData>WAVO:alert=FALSE</xn:vendorData>
    <xn:vendorData>WAVO:headline_only=FALSE</xn:vendorData>
    <xn:vendorData>WAVO:temporary=FALSE</xn:vendorData>
    <xn:vendorData>AMX:Publish Reason=CORRECTED</xn:vendorData>
    <xn:vendorData>AMX:Alert=FALSE</xn:vendorData>
    <xn:vendorData>AMX:Headline Only=FALSE</xn:vendorData>
    <xn:vendorData>AMX:Temporary=FALSE</xn:vendorData>
    <xn:vendorData>AMX:Special Code=PS/p.TELEGR__</xn:vendorData>
    <xn:vendorData>AMX:Special Code=PS/s.ONLINE__</xn:vendorData>
    <xn:copyright>Copyright © 2020 Telegraph.co.ukk. All rights reserved</xn:copyright>
    <!-- Entity Extractor -->
    <xn:companyCode>ACORN:A.3601037911#6#60#60</xn:companyCode>
    <xn:companyCode>ACORN:A.2295203068#6#60#60</xn:companyCode>
    <xn:industryCode>IC/fini#6#50#60</xn:industryCode>
    <xn:industryCode>IC/fini.bank#6#60#60</xn:industryCode>
    <xn:industryCode>IC/fini.invs#6#60#60</xn:industryCode>
    <xn:industryCode>IC/fini.secr#6#60#60</xn:industryCode>
    <xn:industryCode>IC/svcs#6#50#60</xn:industryCode>
    <xn:industryCode>IC/svcs.prof#6#60#60</xn:industryCode>
    <xn:locationCode>LB/car#7#70#49</xn:locationCode>
    <xn:locationCode>LR/car#9#70#90</xn:locationCode>
    <xn:locationCode>LU/car#9#70#90</xn:locationCode>
    <xn:locationCode>LU/car.any#7#49#70</xn:locationCode>
    <xn:subjectCode>NZ/COID#6#50#60</xn:subjectCode>
    <xn:subjectCode>NZ/COID.1475554280#6#60#60</xn:subjectCode>
    <xn:subjectCode>NZ/COID.27088#6#60#60</xn:subjectCode>
    <xn:subjectCode>NZ/COID.5838940#6#60#60</xn:subjectCode>
    <!-- Classifier -->
    <xn:subjectCode>IS/lifesoc.privair#5#50#50</xn:subjectCode>
    <xn:subjectCode>MC/HOT#6</xn:subjectCode>
    <xn:subjectCode>NC/67115358#9#98#50</xn:subjectCode>
    <xn:subjectCode>NC/67115586#5#55#50</xn:subjectCode>
    <xn:subjectCode>NC/67119129#5#58#50</xn:subjectCode>
    <xn:subjectCode>NC/67119169#5#50#50</xn:subjectCode>
    <xn:vendorData>AMX:Special Code=PT/updated</xn:vendorData>
    <xn:subjectCode>XC/any#6#50#60</xn:subjectCode>
    <xn:subjectCode>XC/any.company#6#60#50</xn:subjectCode>
    <xn:subjectCode>XC/Private#6#60#50</xn:subjectCode>
    <!-- Rules -->
    <xn:subjectCode>MC/BIZREL#1</xn:subjectCode>
    <xn:subjectCode>NE/BAYERINS#5#58#50</xn:subjectCode>
    <xn:subjectCode>NE/GEOAMER#9#70#90</xn:subjectCode>
    <xn:subjectCode>NE/GEOCARIB#9#70#90</xn:subjectCode>
    <xn:industryCode>NI/Banks#6#60#60</xn:industryCode>
    <xn:industryCode>NI/Finance#6#60#60</xn:industryCode>
    <xn:industryCode>NI/Securities#6#60#60</xn:industryCode>
    <xn:industryCode>NI/Services#6#60#60</xn:industryCode>
    <xn:vendorData>AMX:Special Code=TL/americas#7#70#50</xn:vendorData>
    <xn:vendorData>AMX:Special Code=TL/LOC#7#50#70</xn:vendorData>
    <xn:vendorData>AMX:Special Code=TT/TOPIC#5#50#50</xn:vendorData>
    <xn:vendorData>AMX:Special Code=TT/transport#5#50#50</xn:vendorData>
    <xn:language>en</xn:language>
</xn:Resource>
</document>

Nosso processamento precisa analisar esses documentos, então estamos tentando normalizar vários atributos de metadados para várias tabelas e colunas.

Apenas analisando xml desconhecido, presumo que o Sql Server tenha que começar com uma tabela de nomes em branco para cada documento analisado; Achei que uma coluna xml digitada começa com um vocabulário conhecido e deve ser mais rápida. Além disso, a esperança era que o xquery também fosse mais rápido.

Aqui está um exemplo das consultas que fazemos no processamento:

;WITH XMLNAMESPACES ('http://www.xmlnews.org/namespaces/meta#' AS xn)
Insert Into dbo.NewsStory
Select NewsID,provider,service,
    CASE When provider='AMSPIDER' and Service='ACBJ' and PublicationAbbrev='web.site' Then dbo.fnGetSpiderPubAbbrev(PublicationAbbrev_Spider) Else PublicationAbbrev End As PublicationAbbrev,
    Title, PublishDate, AMXReceivedTime, AllowedReleaseTime,ParsedDate,DateLine, Description, [Language], PublishReason, IsAlert, IsHeadLine, IsTemporary, Copyright
    From (
        Select  X.NewsID, 
            replace(RIGHT(RS.c.value('(./xn:vendorData[substring((./text())[1],1,22)="AMX:Special Code=PS/p."]/text())[1]', 'VARCHAR(50)'),8)   , '_', '') as provider, 
            replace(RIGHT(RS.c.value('(./xn:vendorData[substring((./text())[1],1,22)="AMX:Special Code=PS/s."]/text())[1]', 'VARCHAR(50)'),8)   , '_', '') as service,
             CONVERT(NVARCHAR(max),RS.c.query('xn:vendorData'))) as PublicationAbbrev,
            replace(RS.c.value('(./xn:vendorData[substring((./text())[1],1,11)="AMX:Credit="]/text())[1]', 'VARCHAR(200)'),'AMX:Credit=', '')  as PublicationAbbrev_Spider,
            RS.c.value('(./xn:title/text())[1]', 'VARCHAR(200)') AS Title,
      CONVERT(DATETIME,REPLACE(LEFT(RS.c.value('(./xn:publicationTime/text())[1]', 'VARCHAR(50)'),19),'T',' ')) AS PublishDate,
        CONVERT(DATETIME,REPLACE(LEFT(RS.c.value('(./xn:receivedTime/text())[1]', 'VARCHAR(50)'),19),'T',' ')) AS AMXReceivedTime,
        CONVERT(DATETIME,REPLACE(LEFT(RS.c.value('(./xn:releaseTime/text())[1]', 'VARCHAR(50)'),19),'T',' ')) AS AllowedReleaseTime, getdate() as ParsedDate,
        RS.c.value('(./xn:dateline/text())[1]', 'VARCHAR(200)') AS DateLine,
        RS.c.value('(./xn:description/text())[1]', 'VARCHAR(2000)') AS Description,
        RS.c.value('(./xn:language/text())[1]', 'VARCHAR(10)') AS [Language],
        LTRIM(SUBSTRING(RS.c.value('(./xn:vendorData[substring((.)[1],1,19)="AMX:Publish Reason="])[1]','VARCHAR(45)'),20,25)) AS PublishReason,
       CASE LTRIM(SUBSTRING(RS.c.value('(./xn:vendorData[substring((./text())[1],1,10)="AMX:Alert="]/text())[1]','VARCHAR(45)'),11,10)) WHEN 'FALSE' THEN 0 ELSE 1 END AS IsAlert,
       CASE LTRIM(SUBSTRING(RS.c.value('(./xn:vendorData[substring((./text())[1],1,18)="AMX:Headline Only="]/text())[1]','VARCHAR(45)'),19,10)) WHEN 'FALSE' THEN 0 ELSE 1 END AS IsHeadLine,
       CASE LTRIM(SUBSTRING(RS.c.value('(./xn:vendorData[substring((./text())[1],1,14)="AMX:Temporary="]/text())[1]','VARCHAR(45)'),15,10)) WHEN 'FALSE' THEN 0 ELSE 1 END AS IsTemporary,
       RS.c.value('(./xn:copyright/text())[1]', 'VARCHAR(1000)')AS Copyright
       From @XmlFileTable X CROSS APPLY AMXFile.nodes('/document/xn:Resource') RS(c)
    ) A

A coleção de esquema vem da fonte NITF ( https://www.iptc.org/std/NITF/3.6/specification/nitf-3-6.xsd ) e do xmlnews dtd ( http://www.xmlnews.org/ dtds/xmlnews-meta-dtd.zip ).

Eu usei o Visual Studio para converter o xmlnews dtd em um esquema e usei isso para propagar NitfSchemaCollection.

Então eu ajustei o esquema NITF para

  • remova o include (aparentemente um pequeno subconjunto para Ruby que eu não precisava)

  • adicionado ao cabeçalho

     ... xmlns:xn="http://www.xmlnews.org/namespaces/meta#">

    <import namespace="http://www.xmlnews.org/namespaces/meta#" />

  • adicionou um elemento de documento logo acima da declaração do elemento nitf , para corresponder ao que o fornecedor está enviando para nós. por exemplo

    <element name="document"> <complexType> <sequence> <element ref="nitf:nitf" minOccurs="1" maxOccurs="1" /> <element ref="xn:Resource" minOccurs="1" maxOccurs ="1" /> </sequência> </complexType> </element>

Cada documento tem apenas 1 nó nitf e 1 nó xn:Resource , mas pode haver muitas instâncias de nós filho em xn:Resource.

sql-server xml
Martin Hope
Lennart - Slava Ukraini
Asked: 2020-04-16 22:02:19 +0800 CST
  • 0

EDIT: demonstrei minha ignorância confundindo atributo com elemento, atualizei minha pergunta. Desculpe. Para esclarecer, estou procurando a existência do código do elemento em:

<conditionmessage:code>MYCODE</conditionmessage:code>

Eu tenho um monte de esquemas onde há uma tabela que contém uma coluna XML. Há alguns índices XML em cada coluna. Tenho certeza de que os esquemas XML diferem entre os esquemas SQL, no entanto, os mesmos índices XML existem em cada esquema. Suspeito copiar/colar e não consigo encontrar nenhuma documentação para os esquemas XML. Então, pensei em uma abordagem mais pragmática, ou seja, percorrer as colunas XML e verificar se cada elemento existe ou não. As tabelas já existem há algum tempo, então todos os elementos possíveis já devem estar lá.

Agora, a pergunta, qual é a melhor maneira de verificar a existência de um elemento, independentemente do valor? Infelizmente, meu cérebro é incompatível com XPATH/XQUERY, e tenho que lutar sempre para me convencer de que a consulta faz o que deveria fazer. Minha abordagem atual parece funcionar, mas estou curioso para saber se existe uma maneira melhor. A cardinalidade da menor tabela é ~ 100.000 linhas e a maior ~ 100.000.000 linhas:

import ibm_db
import ibm_db_dbi
...
conn = ibm_db_dbi.connect("...")
c1 = conn.cursor()

for schema in [ "S1", "S2", ... ]:
    for attribute in [ "a1", "s2", ...]:
        sql = f"""
            select * from {schema}.ATOM_ENTRY
            WHERE XMLEXISTS('$d//*:{attribute}[fn:matches(text(), $p, "i")]' 
                PASSING ENTRY_XML AS "d", CAST('\S*' AS VARCHAR(128)) as "p")
            fetch first 1 rows only
            optimize for 1 rows"""
        c1.execute(sql)
        exists = False
        for row in c1.fetchall():
            exists = True
        print(f"{schema}.ATOM_ENTRY -> {attribute} : {exists}")
db2 xml
Martin Hope
LP.
Asked: 2020-04-04 05:10:02 +0800 CST
  • 2

Contexto:

Eu criei um dump de uma tabela sql em XML (incluindo dados e esquema) usando este comando no prompt de comando.

BCP "SELECT * FROM Database.Schema.TableName FOR XML AUTO, XMLSCHEMA" queryout  C:\data.xml -w -r "" -S localhost -T

Agora que estou equipado com o arquivo XML contendo todas as informações que preciso, gostaria de carregá-lo em outro banco de dados.

Como é tão fácil criar esse dump de tabela em um arquivo xml, suponho que deve haver uma maneira simples de carregar o arquivo XML em um banco de dados (cria a tabela e depois carrega os dados). Alguém sabe como fazer isso?

Isenção de responsabilidade: não sou um DBA e esta é minha primeira experiência com o BCP, a documentação falava sobre a criação de um arquivo Format, mas não parecia funcionar para mim.

Agradecemos antecipadamente por suas respostas

sql-server xml
Martin Hope
Dan
Asked: 2020-03-20 08:33:01 +0800 CST
  • 0

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)
sql-server-2008 xml
Martin Hope
Cory Baumer
Asked: 2020-02-08 19:28:01 +0800 CST
  • 0

Eu tenho uma tabela, chamada product_variants , com uma coluna inteira e uma coluna xml contendo um conjunto de valores de chave estrangeira.

Se eu corresse,

select product_id, options_xml from product_variants

Este seria um resultado típico

product_id  |  options_xml
132         |  <options><option_id>1</option_id><option_id>8</option_id></options>

Eu gostaria de selecionar de alguma forma uma nova linha para cada option_id.

O resultado que eu quero,

product_id  |  option_id
132         |  1
132         |  8

Agradeço antecipadamente!

postgresql xml
Martin Hope
david
Asked: 2020-01-11 01:57:44 +0800 CST
  • 1

Acho que estou quase lá com isso, mas cheguei a um impasse!

Eu tenho uma estrutura XML relativamente simples que preciso analisar como um conjunto de resultados SQL, mas não consigo fazê-la funcionar.

Aqui está o código:

DECLARE @xml XML;
SET @xml = '
<values>
    <Nationality>
        <item>
            <key>UK</key>
            <value>def</value>
        </item>
        <item>
            <key>IE</key>
            <value>def</value>
        </item>
    </Nationality>
    <Currency>
        <item>
            <key>GBP</key>
            <value>123</value>
        </item>
        <item>
            <key>EUR</key>
            <value>345</value>
        </item>
    </Currency>
</values>';

select I.X.value('(/key/text())[1]', 'int') as ItemID,
       I.X.value('(/value/text())[1]', 'int') as Store
from @XML.nodes('/values/Nationality') as O(X)
cross apply O.X.nodes('/item') as I(X);

Essencialmente, quero extrair os valores de Nacionalidade em:

Key  | Value
-------------
UK   | Def
IE   | Def

Eu posso querer fazer o Currency mais tarde, mas acho que se eu conseguir o acima, o resto será simples o suficiente.

Você pode ajudar por favor?

t-sql xml
Martin Hope
Lava Viperidae
Asked: 2019-10-09 15:19:48 +0800 CST
  • 1

Eu tenho uma tabela de banco de dados chamada Warehouses que contém linhas de dados relacionados a armazéns específicos. Uma coluna nessa tabela é denominada itemStats e contém dados XML no seguinte formato:

<itemStats xmlns="http://www.blahblahblah.com/blah">
  <itemCounts>
    <item>
      <name>Toaster Oven Device</name>
      <count>6</count>
    </item>
    <item>
      <name>Washing Machine</name>
      <count>2</count>
    </item>
    <item>
      <name>Microwave Oven</name>
      <count>4</count>
    </item>
  </itemCounts>

Gostaria de consultar a tabela para verificar nessa coluna em cada linha a existência de alguma string (exemplo: Forno) e se encontrar, retornar a contagem associada a ela. Portanto, se eu tivesse os dados acima em itemStats para uma determinada linha em Warehouses , gostaria que ele retornasse o seguinte junto com outras correspondências de outras linhas:

Name                Count
=========================
Toaster Oven Device     6
Microwave Oven          4

Eu tentei várias vezes usando SQL value() e nodes(), mas estou tendo problemas para produzir os resultados desejados. Eu sei que o [1] está errado na consulta abaixo, mas não tenho certeza de como fazer referência a um local dinâmico:

;WITH XMLNAMESPACES (N'http://www.blahblahblah.com/blah' as X)
SELECT itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:name)[1]', 'nvarchar(max)') as Name,
       itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:count)[1]', 'int') as Count
FROM Warehouses
WHERE itemStats.exist('(/X:itemStats/X:itemCounts/X:item/X:name[contains(., "Oven")])') = 1
sql-server-2012 xml