我無法在節點 UsagePoin/mRID 中檢索值,該值是節點 MeterReading 的子節點和節點 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>');
所以我使用下面的代碼。
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'
它為我提供了每個時間戳 = '2022-01-08T00:00:00.000 01:00' 的輸出。現在我需要添加 UsagePoint 和 ReadingType 節點。它們都處于父節點級別。
我嘗試使用以下遍歷層次結構
ir.value('(./mr:ReadingType/@ref)[1]','nvarchar(35)')
它不起作用。我假設使用 ./ 會從節點 IntervalReadings 內向上遍歷到節點 IntervalBlocks 內,然后應該是 mr:ReadingType 來遍歷節點 ReadingType ,然后是 @ref 以獲取“ref”標簽中的值。
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)
uj5u.com熱心網友回復:
請嘗試以下解決方案。
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'
輸出
------------------------------- ---------------- --------------------- ------------ -------------------------------------
| 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. |
------------------------------- ---------------- --------------------- ------------ -------------------------------------
轉載請註明出處,本文鏈接:https://www.uj5u.com/houduan/425380.html
標籤:sql-server xml tsql xquery
