CODEWITHSUNDEEP
sql-serverxpathxquery
drinky
drinky

Reputation: 91

Xquery to return all nodes defined in the outer apply not just [1]

I have some XML

<doc:document xmlns:doc="http://www.elsevier.com/xml/document/schema" xmlns:dp="http://www.elsevier.com/xml/common/doc-properties/schema" xmlns:cps="http://www.elsevier.com/xml/common/consyn-properties/schema" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:dct="http://purl.org/dc/terms/" xmlns:prism="http://prismstandard.org/namespaces/basic/2.0/" xmlns:oa="http://vtw.elsevier.com/data/ns/properties/OpenAccess-1/" xmlns:cp="http://vtw.elsevier.com/data/ns/properties/Copyright-1/" xmlns:cja="http://www.elsevier.com/xml/cja/schema" xmlns:ja="http://www.elsevier.com/xml/ja/schema" xmlns:bk="http://www.elsevier.com/xml/bk/schema" xmlns:ce="http://www.elsevier.com/xml/common/schema" xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:cals="http://www.elsevier.com/xml/common/cals/schema" xmlns:tb="http://www.elsevier.com/xml/common/table/schema" xmlns:sa="http://www.elsevier.com/xml/common/struct-aff/schema" xmlns:sb="http://www.elsevier.com/xml/common/struct-bib/schema" xmlns:xlink="http://www.w3.org/1999/xlink">
  <rdf:RDF>
    <rdf:Description rdf:about="http://dx.doi.org/10.1016/j.actatropica.2015.05.007">
      <dct:format>application/xml</dct:format>
      <dct:title>Evidence and importance of genetic exchange among field populations of Trypanosoma cruzi</dct:title>
      <dct:creator>Louisa A. Messenger</dct:creator>
      <dct:creator>Michael A. Miles</dct:creator>
      <dct:subject>
        <rdf:Bag>
          <rdf:li>Chagas disease</rdf:li>
          <rdf:li>Trypanosoma cruzi</rdf:li>
          <rdf:li>Genetic exchange</rdf:li>
          <rdf:li>Recombination</rdf:li>
          <rdf:li>Cryptic sexuality</rdf:li>
          <rdf:li>Mitochondrial introgression</rdf:li>
          <rdf:li>Clonality</rdf:li>
        </rdf:Bag>
      </dct:subject>
      <dct:description>Acta Tropica, Corrected proof. doi:10.1016/j.actatropica.2015.05.007</dct:description>
      <prism:aggregationType>journal</prism:aggregationType>
      <prism:publicationName>Acta Tropica</prism:publicationName>
      <prism:copyright>Copyright &#169; 2015 The Authors Published by Elsevier B.V. All rights reserved.</prism:copyright>
      <dct:publisher>Elsevier B.V.</dct:publisher>
      <prism:issn>0001-706X</prism:issn>
      <prism:doi>10.1016/j.actatropica.2015.05.007</prism:doi>
      <prism:url>http://dx.doi.org/10.1016/j.actatropica.2015.05.007</prism:url>
      <dct:identifier>doi:10.1016/j.actatropica.2015.05.007</dct:identifier>
      <oa:openAccessInformation>
        <oa:openAccessStatus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">http://vtw.elsevier.com/data/voc/oa/OpenAccessStatus#Full</oa:openAccessStatus>
        <oa:openAccessEffective xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2015-07-14T13:32:10Z</oa:openAccessEffective>
        <oa:sponsor xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <oa:sponsorName>Wellcome Trust</oa:sponsorName>
          <oa:sponsorType>http://vtw.elsevier.com/data/voc/oa/SponsorType#FundingBody</oa:sponsorType>
        </oa:sponsor>
        <oa:userLicense xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">http://creativecommons.org/licenses/by/4.0/</oa:userLicense>
      </oa:openAccessInformation>
    </rdf:Description>
  </rdf:RDF>
</doc:document>

All i want to do is query the creators and return a row per creator

this will cross-tab them

select
    X.Filename
    ,Description.value('(*:doi/text())[1]','nvarchar(100)') doi
    ,Description.value('(*:creator/text())[1]','nvarchar(255)') creator
    ,Description.value('(*:creator/text())[2]','nvarchar(255)') creator2
from
    XMLwithOpenXML X
    OUTER APPLY XMLData.nodes('/*:document/*:RDF/*:Description')dsc(Description)

This query gives me one row

Filename                doi                                 creator      creator2
S0001706X15001412.xml   10.1016/j.actatropica.2015.05.007   Louisa A. Messenger Michael A. Miles

but what i want to do is get

select
    X.Filename
    ,Description.value('(*:doi/text())[1]','nvarchar(100)') doi
    ,Description.value('(*:creator/text())[X]','nvarchar(255)') creator
from
    XMLwithOpenXML X
    OUTER APPLY XMLData.nodes('/*:document/*:RDF/*:Description')dsc(Description)

where i will get a row per author.

where [X] means get all creator nodes.

Any advice?

Upvotes: 1

Views: 83

Answers (1)

har07
har07

Reputation: 89305

You can add another APPLY to shred on creator elements so that you get one row for each creator:

select
    X.Filename
    ,Description.value('(*:doi/text())[1]','nvarchar(100)') doi
    ,creator.value('.','nvarchar(255)') creator
from XMLwithOpenXML X
    OUTER APPLY XMLData.nodes('/*:document/*:RDF/*:Description')dsc(Description)
    OUTER APPLY Description.nodes('*:creator') c(creator)

Upvotes: 2

Related Questions