sql-serverxmlt-sqlxquery
Retreiving value from node in parent level
I am having trouble retrieving value in node UsagePoin/mRID that is child of node MeterReading and sibling of node IntervalBlocks.
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xml_data XML);
INSERT INTO @tbl (xml_data) VALUES
(N'<RequestMessage xmlns="http://iec.ch/TC57/2011/schema/message" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Message.xsd">
<Header>
<Verb>create</Verb>
<Noun>MeterReadings</Noun>
<Timestamp>2022-01-09T01:00:58+01:00</Timestamp>
<Source>Y</Source>
<AsyncReplyFlag>true</AsyncReplyFlag>
<AckRequired>true</AckRequired>
<MessageID>xyz_9999</MessageID>
<CorrelationID></CorrelationID>
</Header>
<Payload>
<MeterReadings xmlns="http://iec.ch/TC57/2011/MeterReadings#" xmlns:MeterReadings="http://iec.ch/TC57/2011/MeterReadings#">
<MeterReading>
<IntervalBlocks>
<IntervalReadings>
<source>999</source>
<timeStamp>2022-01-08T00:00:00.000+01:00</timeStamp>
<value>9.246</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0" />
</ReadingQualities>
</IntervalReadings>
<IntervalReadings>
<source>999</source>
<timeStamp>2022-01-08T01:00:00.000+01:00</timeStamp>
<value>10.404</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0" />
</ReadingQualities>
</IntervalReadings>
<ReadingType ref="11.0.7.1.1.2.12.1.1.0.0.0.0.1010.0.3.72.0" />
</IntervalBlocks>
<Meter>
<mRID>209558305</mRID>
</Meter>
<UsagePoint>
<mRID>735999999999999999</mRID>
</UsagePoint>
</MeterReading>
<MeterReading>
<IntervalBlocks>
<IntervalReadings>
<source>999</source>
<timeStamp>2022-01-08T00:00:00.000+01:00</timeStamp>
<value>2.46</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0" />
</ReadingQualities>
</IntervalReadings>
<IntervalReadings>
<source>999</source>
<timeStamp>2022-01-08T01:00:00.000+01:00</timeStamp>
<value>2.52</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0" />
</ReadingQualities>
</IntervalReadings>
<IntervalReadings>
<source>999</source>
<timeStamp>2022-01-08T02:00:00.000+01:00</timeStamp>
<value>2.502</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0" />
</ReadingQualities>
</IntervalReadings>
<ReadingType ref="11.0.7.1.1.2.12.1.1.0.0.0.0.1102.0.3.73.0" />
</IntervalBlocks>
<Meter>
<mRID>999999999</mRID>
</Meter>
<UsagePoint>
<mRID>735999999999999999</mRID>
</UsagePoint>
</MeterReading>
</MeterReadings>
</Payload>
</RequestMessage>');
So I am using the following code.
WITH XMLNAMESPACES(N'http://iec.ch/TC57/2011/schema/message' as hdr,
N'http://iec.ch/TC57/2011/MeterReadings#' as mr)
SELECT
--t.file_name,t.file_created_time received_timestamp,
--h.value('(hdr:Timestamp)[1]', 'nvarchar(35)') AS created_timestamp,
--h.value('(hdr:MessageID)[1]', 'nvarchar(50)') AS message_id,
ir.value('(mr:timeStamp/text())[1]', 'nvarchar(35)') interval_timestamp,
ir.value('(mr:value/text())[1]', 'nvarchar(35)') interval_value,
ir.value('(mr:ReadingQualities/mr:ReadingQualityType/@ref)[1]', 'nvarchar(35)') interval_value_code
FROM
load.capgemeni_sesp_xml t
OUTER APPLY
t.xml_data.nodes('/hdr:RequestMessage/hdr:Header') AS m(h)
--OUTER APPLY
-- t.xml_data.nodes('/hdr:RequestMessage/hdr:Payload/mr:MeterReadings/mr:MeterReading') AS MeterReading(mr)
OUTER APPLY
t.xml_data.nodes('/hdr:RequestMessage/hdr:Payload/mr:MeterReadings/mr:MeterReading/mr:IntervalBlocks/mr:IntervalReadings') AS IntervalReadings(ir)
where t.file_name = 'SESP_32717237.xml'
AND ir.value('(mr:timeStamp/text())[1]', 'nvarchar(35)') = '2022-01-08T00:00:00.000+01:00'
It gives me output for each timestamp = '2022-01-08T00:00:00.000+01:00'. Now i need to add the UsagePoint and the ReadingType nodes. Both of them are in parent node level.
I tried traversing up hierarchy using following
ir.value('(./mr:ReadingType/@ref)[1]','nvarchar(35)')
It doesn't work. I assumed using ./ would traverse up from within node IntervalReadings to being within node IntervalBlocks and then it should be mr:ReadingType to traverse into node ReadingType and then @ref for getting the value in the "ref" tag.
I can't figure out why it isn't working. I am having similar issue with getting data from node mRID which is within node UsagePoint which is sibling to node IntervalBlocks within node MeterReading. For each UsagePoint can be 1 or more ReadingType which then have interval values on same level.
Whatever working solution I find for ReadingType will be able to be used to also get values for UsagePoint.
EDIT: To clarify i can produce out that looks like this...
It is showing values for the timestamp '2022-01-08T00:00:00.000+01:00' but i can't add ReadingType or UsagePoint correctly. When i for example try to add ReadingType i get null...
EDIT2: For each IntervalBlocks node you have IntervalReadings node which then has x amount of interval values and on same level as IntervalBlocks node you will have the UsagePoint for the values and within the IntervalBlocks node on same level as IntervalReadings will have 1 or more ReadingType node. The ReadingType will be after a payload of values signifying the ReadingType is for that batch of values. 1 UsagePoint can have up to 4 ReadingType values. I should have clarified data modell by having at least 2 UsagePoint and 2 ReadingType in the XML.
I am running SQL Server 2019. Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64) Nov 3 2021 19:19:51 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
Solution
Please try the following solution.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xml_data XML);
INSERT INTO @tbl (xml_data) VALUES
(N'<RequestMessage xmlns="http://iec.ch/TC57/2011/schema/message" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Message.xsd">
<Header>
<Verb>create</Verb>
<Noun>MeterReadings</Noun>
<Timestamp>2022-01-09T01:00:58+01:00</Timestamp>
<Source>Y</Source>
<AsyncReplyFlag>true</AsyncReplyFlag>
<AckRequired>true</AckRequired>
<MessageID>xyz_9999</MessageID>
<CorrelationID></CorrelationID>
<Property>
<Name>CONTRACT</Name>
<Value>xxxx</Value>
</Property>
<Property>
<Name>DIVISION</Name>
<Value>01</Value>
</Property>
<Property>
<Name>RECEIVER</Name>
<Value>xxx</Value>
</Property>
<Property>
<Name>SERVICE</Name>
<Value>xxxx</Value>
</Property>
</Header>
<Payload>
<MeterReadings xmlns="http://iec.ch/TC57/2011/MeterReadings#" xmlns:MeterReadings="http://iec.ch/TC57/2011/MeterReadings#">
<MeterReading>
<IntervalBlocks>
<IntervalReadings>
<source>x</source>
<timeStamp>2022-01-08T00:00:00.000+01:00</timeStamp>
<value>9.246</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0"/>
</ReadingQualities>
</IntervalReadings>
<IntervalReadings>
<source>x</source>
<timeStamp>2022-01-08T01:00:00.000+01:00</timeStamp>
<value>10.404</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0"/>
</ReadingQualities>
</IntervalReadings>
<ReadingType ref="11.0.7.1.1.2.12.1.1.0.0.0.0.1010.0.3.72.0"/>
</IntervalBlocks>
<IntervalBlocks>
<IntervalReadings>
<source>x</source>
<timeStamp>2022-01-08T00:00:00.000+01:00</timeStamp>
<value>9.246</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0"/>
</ReadingQualities>
</IntervalReadings>
<IntervalReadings>
<source>x</source>
<timeStamp>2022-01-08T01:00:00.000+01:00</timeStamp>
<value>10.404</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0"/>
</ReadingQualities>
</IntervalReadings>
<ReadingType ref="11.0.7.1.1.2.12.1.1.0.0.0.0.1102.0.3.72.0"/>
</IntervalBlocks>
<Meter>
<mRID>xxxx</mRID>
</Meter>
<UsagePoint>
<mRID>zzzz</mRID>
</UsagePoint>
</MeterReading>
<MeterReading>
<IntervalBlocks>
<IntervalReadings>
<source>x</source>
<timeStamp>2022-01-08T00:00:00.000+01:00</timeStamp>
<value>9.246</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0"/>
</ReadingQualities>
</IntervalReadings>
<IntervalReadings>
<source>x</source>
<timeStamp>2022-01-08T01:00:00.000+01:00</timeStamp>
<value>10.404</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0"/>
</ReadingQualities>
</IntervalReadings>
<ReadingType ref="11.0.7.1.1.2.12.1.1.0.0.0.0.1010.0.3.72.0"/>
</IntervalBlocks>
<IntervalBlocks>
<IntervalReadings>
<source>x</source>
<timeStamp>2022-01-08T00:00:00.000+01:00</timeStamp>
<value>9.246</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0"/>
</ReadingQualities>
</IntervalReadings>
<IntervalReadings>
<source>x</source>
<timeStamp>2022-01-08T01:00:00.000+01:00</timeStamp>
<value>10.404</value>
<ReadingQualities>
<ReadingQualityType ref="3.0.0"/>
</ReadingQualities>
</IntervalReadings>
<ReadingType ref="11.0.7.1.1.2.12.1.1.0.0.0.0.1102.0.3.72.0"/>
</IntervalBlocks>
<Meter>
<mRID>kkkkk</mRID>
</Meter>
<UsagePoint>
<mRID>qqqqq</mRID>
</UsagePoint>
</MeterReading>
</MeterReadings>
</Payload>
</RequestMessage>');
-- DDL and sample data population, end
WITH XMLNAMESPACES(N'http://iec.ch/TC57/2011/schema/message' as hdr,
N'http://iec.ch/TC57/2011/MeterReadings#' as mr)
SELECT
--t.file_name,t.file_created_time received_timestamp,
--h.value('(hdr:Timestamp)[1]', 'nvarchar(35)') AS created_timestamp,
--h.value('(hdr:MessageID)[1]', 'nvarchar(50)') AS message_id,
ir.value('(mr:timeStamp/text())[1]', 'nvarchar(35)') interval_timestamp
, ir.value('(mr:value/text())[1]', 'nvarchar(35)') interval_value
, ir.value('(mr:ReadingQualities/mr:ReadingQualityType/@ref)[1]', 'nvarchar(35)') interval_value_code
, mr.value('(mr:UsagePoint/mr:mRID/text())[1]', 'nvarchar(50)') UsagePoint
, ir.value('(../mr:ReadingType/@ref)[1]', 'nvarchar(35)') ReadingType
FROM @tbl t
OUTER APPLY t.xml_data.nodes('/hdr:RequestMessage/hdr:Header') AS m(h)
OUTER APPLY t.xml_data.nodes('/hdr:RequestMessage/hdr:Payload/mr:MeterReadings/mr:MeterReading') AS MeterReading(mr)
OUTER APPLY MeterReading.mr.nodes('mr:IntervalBlocks/mr:IntervalReadings') AS IntervalReadings(ir)
--WHERE --t.file_name = 'SESP_32717237.xml' AND
-- ir.value('(mr:timeStamp/text())[1]', 'nvarchar(35)') = '2022-01-08T00:00:00.000+01:00'
Output
+-------------------------------+----------------+---------------------+------------+-------------------------------------+
| interval_timestamp | interval_value | interval_value_code | UsagePoint | ReadingType |
+-------------------------------+----------------+---------------------+------------+-------------------------------------+
| 2022-01-08T00:00:00.000+01:00 | 9.246 | 3.0.0 | zzzz | 11.0.7.1.1.2.12.1.1.0.0.0.0.1010.0. |
| 2022-01-08T01:00:00.000+01:00 | 10.404 | 3.0.0 | zzzz | 11.0.7.1.1.2.12.1.1.0.0.0.0.1010.0. |
| 2022-01-08T00:00:00.000+01:00 | 9.246 | 3.0.0 | zzzz | 11.0.7.1.1.2.12.1.1.0.0.0.0.1102.0. |
| 2022-01-08T01:00:00.000+01:00 | 10.404 | 3.0.0 | zzzz | 11.0.7.1.1.2.12.1.1.0.0.0.0.1102.0. |
| 2022-01-08T00:00:00.000+01:00 | 9.246 | 3.0.0 | qqqqq | 11.0.7.1.1.2.12.1.1.0.0.0.0.1010.0. |
| 2022-01-08T01:00:00.000+01:00 | 10.404 | 3.0.0 | qqqqq | 11.0.7.1.1.2.12.1.1.0.0.0.0.1010.0. |
| 2022-01-08T00:00:00.000+01:00 | 9.246 | 3.0.0 | qqqqq | 11.0.7.1.1.2.12.1.1.0.0.0.0.1102.0. |
| 2022-01-08T01:00:00.000+01:00 | 10.404 | 3.0.0 | qqqqq | 11.0.7.1.1.2.12.1.1.0.0.0.0.1102.0. |
+-------------------------------+----------------+---------------------+------------+-------------------------------------+
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?