Xquery query to retreive values from associated header for detail-CodePudding

I have XML that I am able to retrieve values from but I am getting duplicates with a solution I am using that worked OK for different structured XML.

The XML

<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>1</del:DeliverySiteEANCode>
      <del:SvkCode>901</del:SvkCode>
      <del:MeterId>-1</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:Value position="3" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T02:00:00.000 00:00" requestedReadingDate="2021-08-28T02:00:00.000 00:00" reading="96542.79" status="51" meterReadingId="1459846143" />
        <del:Value position="4" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T03:00:00.000 00:00" requestedReadingDate="2021-08-28T03:00:00.000 00:00" reading="96543.06" status="51" meterReadingId="1459846144" />
        <del:Value position="5" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T04:00:00.000 00:00" requestedReadingDate="2021-08-28T04:00:00.000 00:00" reading="96543.31" status="51" meterReadingId="1459846145" />
        <del:Value position="6" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T05:00:00.000 00:00" requestedReadingDate="2021-08-28T05:00:00.000 00:00" reading="96543.58" status="51" meterReadingId="1459846146" />
        <del:Value position="7" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T06:00:00.000 00:00" requestedReadingDate="2021-08-28T06:00:00.000 00:00" reading="96543.99" status="51" meterReadingId="1459846147" />
        <del:Value position="8" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T07:00:00.000 00:00" requestedReadingDate="2021-08-28T07:00:00.000 00:00" reading="96544.43" status="51" meterReadingId="1459846148" />
        <del:Value position="9" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T08:00:00.000 00:00" requestedReadingDate="2021-08-28T08:00:00.000 00:00" reading="96544.89" status="51" meterReadingId="1459846149" />
        <del:Value position="10" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T09:00:00.000 00:00" requestedReadingDate="2021-08-28T09:00:00.000 00:00" reading="96545.29" status="51" meterReadingId="1459846150" />
        <del:Value position="11" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T10:00:00.000 00:00" requestedReadingDate="2021-08-28T10:00:00.000 00:00" reading="96546.02" status="51" meterReadingId="1459846151" />
        <del:Value position="12" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T11:00:00.000 00:00" requestedReadingDate="2021-08-28T11:00:00.000 00:00" reading="96547.37" status="51" meterReadingId="1459846152" />
        <del:Value position="13" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T12:00:00.000 00:00" requestedReadingDate="2021-08-28T12:00:00.000 00:00" reading="96548.04" status="51" meterReadingId="1459846153" />
        <del:Value position="14" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T13:00:00.000 00:00" requestedReadingDate="2021-08-28T13:00:00.000 00:00" reading="96549.92" status="51" meterReadingId="1459846154" />
        <del:Value position="15" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T14:00:00.000 00:00" requestedReadingDate="2021-08-28T14:00:00.000 00:00" reading="96550.69" status="51" meterReadingId="1459846155" />
        <del:Value position="16" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T15:00:00.000 00:00" requestedReadingDate="2021-08-28T15:00:00.000 00:00" reading="96551.69" status="51" meterReadingId="1459846156" />
        <del:Value position="17" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T16:00:00.000 00:00" requestedReadingDate="2021-08-28T16:00:00.000 00:00" reading="96553.68" status="51" meterReadingId="1459846157" />
        <del:Value position="18" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T17:00:00.000 00:00" requestedReadingDate="2021-08-28T17:00:00.000 00:00" reading="96555.07" status="51" meterReadingId="1459846158" />
        <del:Value position="19" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T18:00:00.000 00:00" requestedReadingDate="2021-08-28T18:00:00.000 00:00" reading="96557.56" status="51" meterReadingId="1459846159" />
        <del:Value position="20" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T19:00:00.000 00:00" requestedReadingDate="2021-08-28T19:00:00.000 00:00" reading="96558.36" status="51" meterReadingId="1459846160" />
        <del:Value position="21" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T20:00:00.000 00:00" requestedReadingDate="2021-08-28T20:00:00.000 00:00" reading="96559.01" status="51" meterReadingId="1459846161" />
        <del:Value position="22" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T21:00:00.000 00:00" requestedReadingDate="2021-08-28T21:00:00.000 00:00" reading="96559.82" status="51" meterReadingId="1459846162" />
        <del:Value position="23" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T22:00:00.000 00:00" requestedReadingDate="2021-08-28T22:00:00.000 00:00" reading="96560.44" status="51" meterReadingId="1459846163" />
        <del:Value position="24" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T23:00:00.000 00:00" requestedReadingDate="2021-08-28T23:00:00.000 00:00" reading="96560.83" status="51" meterReadingId="1459846164" />
      </del:Values>
    </del:Data>
    <del:Data>
      <del:TaskTypeId>0</del:TaskTypeId>
      <del:TaskId>2</del:TaskId>
      <del:DeliverySiteEANCode>2</del:DeliverySiteEANCode>
      <del:SvkCode>901</del:SvkCode>
      <del:MeterId>-1</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>2</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="126748.93" status="50" meterReadingId="1459846165" />
        <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="126749.71" status="50" meterReadingId="1459846166" />
        <del:Value position="3" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T02:00:00.000 00:00" requestedReadingDate="2021-08-28T02:00:00.000 00:00" reading="126750.49" status="50" meterReadingId="1459846167" />
        <del:Value position="4" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T03:00:00.000 00:00" requestedReadingDate="2021-08-28T03:00:00.000 00:00" reading="126751.27" status="50" meterReadingId="1459846168" />
        <del:Value position="5" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T04:00:00.000 00:00" requestedReadingDate="2021-08-28T04:00:00.000 00:00" reading="126752.06" status="50" meterReadingId="1459846169" />
        <del:Value position="6" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T05:00:00.000 00:00" requestedReadingDate="2021-08-28T05:00:00.000 00:00" reading="126752.84" status="50" meterReadingId="1459846170" />
        <del:Value position="7" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T06:00:00.000 00:00" requestedReadingDate="2021-08-28T06:00:00.000 00:00" reading="126753.62" status="50" meterReadingId="1459846171" />
        <del:Value position="8" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T07:00:00.000 00:00" requestedReadingDate="2021-08-28T07:00:00.000 00:00" reading="126754.4" status="50" meterReadingId="1459846172" />
        <del:Value position="9" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T08:00:00.000 00:00" requestedReadingDate="2021-08-28T08:00:00.000 00:00" reading="126755.18" status="50" meterReadingId="1459846173" />
        <del:Value position="10" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T09:00:00.000 00:00" requestedReadingDate="2021-08-28T09:00:00.000 00:00" reading="126755.96" status="50" meterReadingId="1459846174" />
        <del:Value position="11" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T10:00:00.000 00:00" requestedReadingDate="2021-08-28T10:00:00.000 00:00" reading="126756.74" status="50" meterReadingId="1459846175" />
        <del:Value position="12" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T11:00:00.000 00:00" requestedReadingDate="2021-08-28T11:00:00.000 00:00" reading="126757.52" status="50" meterReadingId="1459846176" />
        <del:Value position="13" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T12:00:00.000 00:00" requestedReadingDate="2021-08-28T12:00:00.000 00:00" reading="126758.3" status="50" meterReadingId="1459846177" />
        <del:Value position="14" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T13:00:00.000 00:00" requestedReadingDate="2021-08-28T13:00:00.000 00:00" reading="126759.08" status="50" meterReadingId="1459846178" />
        <del:Value position="15" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T14:00:00.000 00:00" requestedReadingDate="2021-08-28T14:00:00.000 00:00" reading="126759.86" status="50" meterReadingId="1459846179" />
        <del:Value position="16" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T15:00:00.000 00:00" requestedReadingDate="2021-08-28T15:00:00.000 00:00" reading="126760.64" status="50" meterReadingId="1459846180" />
        <del:Value position="17" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T16:00:00.000 00:00" requestedReadingDate="2021-08-28T16:00:00.000 00:00" reading="126761.42" status="50" meterReadingId="1459846181" />
        <del:Value position="18" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T17:00:00.000 00:00" requestedReadingDate="2021-08-28T17:00:00.000 00:00" reading="126762.2" status="50" meterReadingId="1459846182" />
        <del:Value position="19" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T18:00:00.000 00:00" requestedReadingDate="2021-08-28T18:00:00.000 00:00" reading="126762.98" status="50" meterReadingId="1459846183" />
        <del:Value position="20" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T19:00:00.000 00:00" requestedReadingDate="2021-08-28T19:00:00.000 00:00" reading="126763.76" status="50" meterReadingId="1459846184" />
        <del:Value position="21" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T20:00:00.000 00:00" requestedReadingDate="2021-08-28T20:00:00.000 00:00" reading="126764.54" status="50" meterReadingId="1459846185" />
        <del:Value position="22" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T21:00:00.000 00:00" requestedReadingDate="2021-08-28T21:00:00.000 00:00" reading="126765.32" status="50" meterReadingId="1459846186" />
        <del:Value position="23" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T22:00:00.000 00:00" requestedReadingDate="2021-08-28T22:00:00.000 00:00" reading="126766.1" status="50" meterReadingId="1459846187" />
        <del:Value position="24" registrationDate="2021-10-27T22:01:51.000 00:00" readingDate="2021-08-28T23:00:00.000 00:00" requestedReadingDate="2021-08-28T23:00:00.000 00:00" reading="126766.88" status="50" meterReadingId="1459846188" />
      </del:Values>
    </del:Data>
  </del:DataRows>
</del:DeliverMeterReading>

The query

WITH XMLNAMESPACES(DEFAULT N'http://schemas.fortum.com/amm/delivermeterreading')
SELECT DISTINCT
    t.file_name, t.file_created_time received_timestamp
    ,h.value(N'(Header/MessageCreatedTimestamp)[1]', 'varchar(40)') as created_timestamp
    ,h.value(N'(Header/DispatchId)[1]', 'varchar(40)') as dispatch_id
    ,d.value(N'(DeliverySiteEANCode)[1]', 'varchar(40)') ean
    --,d.value(N'(ReadingStartDate)[1]', 'varchar(40)') ReadingStartDate
    --,d.value(N'(TaskId)[1]', 'varchar(40)') taskid
    --,d.value(N'(RecordPosition)[1]', 'varchar(40)') RecordPosition
    ,v.value(N'@position','varchar(35)') position
    ,v.value(N'@reading','varchar(35)') reading
    ,v.value(N'@status','varchar(35)') status
FROM
    load.t t
OUTER APPLY
    t.xml_data.nodes('/DeliverMeterReading') AS h(h)
OUTER APPLY
    t.xml_data.nodes('/DeliverMeterReading/DataRows/Data') AS delsite(d)
OUTER APPLY
    d.nodes('/Values') AS readings(v)

The purpose of the readings apply is to try and do some correlated apply by getting values associated with each del:DeliverySiteEANCode. I only need to do this because I otherwise get some cartesian product or something so it can't match the Values I retrieve to the del:DeliverySiteEANCode those values belong to. Would have been good if there was some way to traverse upwards in XML hierarchy to retrieve values. That way I can start with most granular level and attach the header info for that detail. So in this case when retrieving Value position = "1" I also have it's del:DeliverySiteEANCode connected.

Using SQL Server 2019.

CodePudding user response：

You have two mistakes:

/Values You are starting at the root. If you take off the / it will begin at the current Data node
You aren't descending again to Value nodes

So you should have

OUTER APPLY
    d.nodes('Values/Value') AS readings(v)

There are other efficiencies here:

Adding /text() to each .value is more performant (don't do this for @ attributes)
The first .nodes should refer directly to the Header node
DISTINCT has a performance cost. Don't just chuck DISTINCT at a query to make duplicates go away, think about how they got there in the first place.

WITH XMLNAMESPACES(DEFAULT N'http://schemas.fortum.com/amm/delivermeterreading')
SELECT
    h.value(N'(MessageCreatedTimestamp/text())[1]', 'varchar(40)') as created_timestamp
    ,h.value(N'(DispatchId/text())[1]', 'varchar(40)') as dispatch_id
    ,d.value(N'(DeliverySiteEANCode/text())[1]', 'varchar(40)') ean
    --,d.value(N'(ReadingStartDate/text())[1]', 'varchar(40)') ReadingStartDate
    --,d.value(N'(TaskId/text())[1]', 'varchar(40)') taskid
    --,d.value(N'(RecordPosition/text())[1]', 'varchar(40)') RecordPosition
    ,v.value(N'@position','varchar(35)') position
    ,v.value(N'@reading','varchar(35)') reading
    ,v.value(N'@status','varchar(35)') status
FROM
    dbo.t t
OUTER APPLY
    t.xml_data.nodes('/DeliverMeterReading/Header') AS h(h)
OUTER APPLY
    t.xml_data.nodes('/DeliverMeterReading/DataRows/Data') AS delsite(d)
OUTER APPLY
    delsite.d.nodes('Values/Value') AS readings(v)

db<>fiddle