Início / dba / Perguntas / 271379
Accepted
user1664043
Asked: 2020-07-22 13:20:08 +0800 CST

Misturar ;COM NAMESPACES e coleções de esquema?

  • 772

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

1 respostas

  1. Best Answer

    A parte do XML que você está analisando não é restrita por um esquema, mas por um DTD, portanto, você não pode usar um agrupamento de esquema para tornar a análise pelo SQL Server diferente. Dito isso, ainda não vi um caso em que um esquema seja útil no cenário em que você está destruindo documentos XML em tabelas, e há a adição da sobrecarga de validação do XML em relação ao esquema.

    Há algumas coisas que você pode fazer na consulta para torná-la mais eficiente.

    Na query abaixo alterei o tratamento das datas, movi os text()predicados in antes do predicado e usei .no predicado e usando exist()onde você está verificando os valores booleanos.

    Observe que o que aconteceu comigo nos meus testes foi que a reescrita não foi paralela, portanto, ao comparar o desempenho, lembre-se disso. Você pode gostar que ele use apenas um thread em um servidor ocupado ou pode querer usar tudo o que tem. Se você quiser que a consulta seja paralela, você pode usar um sinalizador de rastreamento OPTION(QUERYTRACEON 8649)ou se preferir um plano serial, use option (maxdop 1).

    Portanto, em meus testes no SQL Server 2008, o desempenho da reescrita é cerca de duas vezes mais rápido.

    Veja o que eu fiz aqui, use se gostar e teste em seus dados.

    with xmlnamespaces ('http://www.xmlnews.org/namespaces/meta#' AS xn)
select replace(right(RS.c.value('(xn:vendorData/text()[substring((.)[1],1,22)="AMX:Special Code=PS/p."])[1]', 'varchar(50)'), 8), '_', '') as provider,
       replace(right(RS.c.value('(xn:vendorData/text()[substring((.)[1],1,22)="AMX:Special Code=PS/s."])[1]', 'varchar(50)'), 8), '_', '') as service,
       convert(nvarchar(max), RS.c.query('xn:vendorData')) as PublicationAbbrev,
       replace(RS.c.value('(xn:vendorData/text()[substring((.)[1],1,11)="AMX:Credit="])[1]', 'VARCHAR(200)'), 'AMX:Credit=', '') as PublicationAbbrev_Spider,
       RS.c.value('(xn:title/text())[1]', 'varchar(200)') as Title,
       convert(datetime, RS.c.value('(xn:publicationTime/text())[1]', 'varchar(19)')) as PublishDate,
       convert(datetime, RS.c.value('(xn:receivedTime/text())[1]', 'varchar(19)')) as AMXReceivedTime,
       convert(datetime, RS.c.value('(xn:releaseTime/text())[1]', 'varchar(19)')) 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/text()[substring((.)[1],1,19)="AMX:Publish Reason="])[1]', 'VARCHAR(45)'), 20, 25)) as PublishReason,
       RS.c.exist('xn:vendorData/text()[. = "AMX:Alert=TRUE"]') as IsAlert,
       RS.c.exist('xn:vendorData/text()[. = "AMX:Headline Only=TRUE"]') as IsHeadLine,
       RS.c.exist('xn:vendorData/text()[. = "AMX:Temporary=TRUE"]') 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);
    • 1

relate perguntas