SQL Server XML namespace colon-CodePudding

I have an XML I am trying to parse to table. I have managed this with similar but different XML.

It looks like this:

<del:DeliverMeterReading xmlns:del="http://schemas.fortum.com/amm/delivermeterreading">
  <del:Header>
    <del:MessageId> X </del:MessageId>
    <del:MessageType> Y </del:MessageType>
    <del:MessageCreatedTimestamp>2021-10-27T22:10:25.362 00:00</del:MessageCreatedTimestamp>
    <del:MessageReceivedTimestamp>2021-10-27T22:10:31 00:00</del:MessageReceivedTimestamp>
    <del:DispatchId> Z </del:DispatchId>
  </del:Header>
  <del:DataRows>
    <del:Data>
      <del:TaskTypeId>0</del:TaskTypeId>
      <del:TaskId>1</del:TaskId>
      <del:DeliverySiteEANCode> M </del:DeliverySiteEANCode>
      <del:SvkCode>901</del:SvkCode>
      <del:MeterId> 999999999 </del:MeterId>
      <del:DeliveryFormat>E</del:DeliveryFormat>
      <del:ReadingStartDate>2021-08-28T00:00:00.000 00:00</del:ReadingStartDate>
      <del:ReadingEndDate>2021-08-28T23:00:00.000 00:00</del:ReadingEndDate>
      <del:Resolution>PT1H</del:Resolution>
      <del:SpSla />
      <del:RecordPosition>1</del:RecordPosition>
      <del:Values>
        <del:Value position="1" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T00:00:00.000 00:00" requestedReadingDate="2021-08-28T00:00:00.000 00:00" reading="96542.26" status="51" meterReadingId="1459846141" />
        <del:Value position="2" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T01:00:00.000 00:00" requestedReadingDate="2021-08-28T01:00:00.000 00:00" reading="96542.54" status="51" meterReadingId="1459846142" />
      </del:Values>
    </del:Data>
  </del:DataRows>
</del:DeliverMeterReading>

The code I have tried to access values is:

WITH XMLNAMESPACES(N'http://schemas.fortum.com/amm/delivermeterreading' AS del)
SELECT
    t.file_name, t.file_created_time received_timestamp,
    h.value(N'(/del:MessageId)[1]','varchar(40)')
FROM
    load.t t
OUTER APPLY
    t.xml_data.nodes('/del:DeliverMeterReading/del:Header') AS m(h)

As t = table with xml_data column. I have tried some variation of select but can't figure out why it is showing null. I am including namespace in both the outer apply path and the value extraction select.

Running SQL Server 2019.

There is similar question XML node values where xml data has colon syntax which was answered by marc_s and I used that but just adjusted to try and get value between tag brackets vs inside tag (@ used for values inside tag).

CodePudding user response：

You have an extra / as mentioned.

Might be easier to use a DEFAULT namespace

WITH XMLNAMESPACES(DEFAULT N'http://schemas.fortum.com/amm/delivermeterreading')
SELECT
    t.file_name, t.file_created_time received_timestamp,
    h.value(N'(MessageId/text())[1]', 'varchar(40)')
FROM
    load.t t
OUTER APPLY
    t.xml_data.nodes('/DeliverMeterReading/Header') AS m(h)

CodePudding user response：

Minimal typo - in the .value(), do not include the leading '/':

WITH XMLNAMESPACES(N'http://schemas.fortum.com/amm/delivermeterreading' AS del)
SELECT
    t.file_name, t.file_created_time received_timestamp,
    h.value(N'(del:MessageId)[1]', 'varchar(40)')
FROM
    load.t t
OUTER APPLY
    t.xml_data.nodes('/del:DeliverMeterReading/del:Header') AS m(h)