文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >Xquery查询从关联的头中提取值以获得详细信息

问Xquery查询从关联的头中提取值以获得详细信息
EN

Stack Overflow用户
提问于 2021-10-30 23:08:16
回答 1查看 87关注 0票数 0

我有能够从XML中检索值的XML，但是我使用的解决方案是重复的，这个解决方案对于不同的结构化XML是可行的。

XML

代码语言：javascript
复制
<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>

查询

代码语言：javascript
复制
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)

读数应用的目的是通过获取与每个del:DeliverySiteEANCode相关联的值来尝试并执行一些相关的应用。我只需要这样做，因为否则我会得到一些笛卡儿乘积或什么的，这样它就不能与我检索到的值与模型:DeliverySiteEANCode匹配，这些值属于这些值。如果在XML层次结构中有一些向上遍历检索值的方法，那就更好了。这样，我就可以从大多数粒度级别开始，并为该细节附加标题信息。因此，在本例中，当检索值position = "1“时，它的del:DeliverySiteEANCode连接。

使用Server 2019。

sql-server
xquery-sql
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-10-31 01:19:02

你有两个错误：

  • /Values从根目录开始。如果您取消/，它将从当前的Data节点开始。
  • 不再降至Value节点

所以你应该

代码语言：javascript
复制
OUTER APPLY
    d.nodes('Values/Value') AS readings(v)

这里还有其他效率：

  • 向每个/text()添加.value更具有性能(不要对@属性这样做)
  • 第一个.nodes应该直接引用Header节点
  • DISTINCT的性能成本很高。不要只是在查询时放弃DISTINCT，让重复的内容消失，想想它们是如何到达的。
代码语言：javascript
复制
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

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/69782800

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档