XQuery to retrieve value-CodePudding

I have this XML that I am trying to retrieve a value from...

<ExportPartyAddressingResponse xmlns="http://www.ediel.no/Export">
  <CompanyListMessage>
    <Header GeneratedAt="2021-11-11T05:00:02Z" SenderApplication="www.ediel.se" />
    <Market Code="EL" CountryCode="SE">
      <Company>
        <Name>7H Kraft EnergifÃ¶rmedling AB</Name>
        <ContactInformation>
          <Item Type="Telephone"> 46321532323</Item>
          <Item Type="Telefax"> 46321532321</Item>
          <Item Type="Web">www.7hkraft.se</Item>
        </ContactInformation>
        <Identifiers>
          <Key Type="EdielId">60900</Key>
          <Key Type="OrgNo">556525-8075</Key>
          <Key Type="SvKId">7HK</Key>
        </Identifiers>
        <Addresses>
          <Address Type="Postal">
            <Address1>Box 25</Address1>
            <PostCode>22100</PostCode>
            <Place>LUND</Place>
            <CountryCode>SE</CountryCode>
          </Address>
        </Addresses>
        <Roles>
          <Role>PowerSupplier</Role>
        </Roles>
        <EdielAddressing>
          <EDIFACTAddressing>
            <EDIFACTDetails Type="PRODAT">
              <EDICharset>UNOC</EDICharset>
              <EDISyntax>3</EDISyntax>
              <CommunicationAddress Type="SMTP">[email protected]</CommunicationAddress>
              <InterchangePartyId IdCodeQualifier="ZZ">60900</InterchangePartyId>
              <PartyId IdCodeQualifier="160" IdCodeResponsible="SVK">60900</PartyId>
            </EDIFACTDetails>
            <EDIFACTDetails Type="UTILTS">
              <EDICharset>UNOC</EDICharset>
              <EDISyntax>3</EDISyntax>
              <CommunicationAddress Type="SMTP">[email protected]</CommunicationAddress>
              <InterchangePartyId IdCodeQualifier="ZZ">60900</InterchangePartyId>
              <PartyId IdCodeQualifier="SVK" IdCodeResponsible="260">60900</PartyId>
            </EDIFACTDetails>
          </EDIFACTAddressing>
          <NBSAddressing>
            <NBSDetails Type="NBS">
              <EnergyPartyId IdentifierType="EdielId">60900</EnergyPartyId>
            </NBSDetails>
          </NBSAddressing>
        </EdielAddressing>
      </Company>
    </Market>
  </CompanyListMessage>
</ExportPartyAddressingResponse>

I have tried the following SQL statement, just to first get info from header tag...

WITH XMLNAMESPACES(DEFAULT N'http://www.ediel.no/Export')
    SELECT
        t.file_name, t.file_created_time,
        h.value(N'(GeneratedAt/text())[1]', 'varchar(50)') h1,
        h.value(N'@GeneratedAt', 'varchar(50)') h2
    FROM
        load.ediel_actors t
    OUTER APPLY
        t.xml_data.nodes('/ExportPartyAddressingResponse/CompanyListMessage') AS header(h)

I can't figure out why my SQL isn't working. Is it the namespace definition or is it the code for retrieving values?

I am using SQL Server 2019.

CodePudding user response：

Two problems:

(1): your XPath expression is wrong - you must also include the /Header part to get the <Header> XML element selected

(2): your h1 expression is wrong - since you're trying to get the value of an attribute on the XML element, you need to use the expression in your second line.

So try this:

WITH XMLNAMESPACES(DEFAULT N'http://www.ediel.no/Export')
    SELECT
        t.Col1,
        h.value(N'@GeneratedAt', 'varchar(50)') HeaderGeneratedAt,
        h.value(N'@SenderApplication', 'varchar(50)') SenderApplication
    FROM
        @datatbl t
    OUTER APPLY
        t.xmldata.nodes('/ExportPartyAddressingResponse/CompanyListMessage/Header') AS header(h)