Xquery query to retreive values from associated header for detail

Question

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.

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Oct 30 '21 at 23:41

Charlieface · Accepted Answer · 2021-10-31T01:40:35.537

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

Thank you, it worked. Can you recommend any good sources to learn xquery? The official documentation from MS is not that easy to get into when compared to ordinary relation sql. For example i have seen in other questions that sometimes text() is used but it is not obvious why that would be used. P.S. I agree i shouldn't have added DISTINCT and instead tried to understand why i was getting duplicate rows. — Mattias W, Oct 31 '21 at 10:57
`text()` is used to get the inner text of a node. It is more performant because otherwise you are effectively converting the whole node, like this `string(SomeNode)`, which is slow. Attributes don't need it because they are by definition a string anyway. I agree the docs could do with improvement, I haven't found much better elsewhere. — Charlieface, Oct 31 '21 at 11:01

Xquery query to retreive values from associated header for detail

1 Answers1