文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >Server命名空间冒号

问Server命名空间冒号
EN

Stack Overflow用户
提问于 2021-10-29 06:28:42
回答 2查看 119关注 0票数 1

我正在尝试将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(N'http://schemas.fortum.com/amm/delivermeterreading' AS del)
SELECT
    t.file_name, t.file_created_time received_timestamp,
    h.value(N'(/del:MessageId)[1]','varchar(40)')
FROM
    load.t t
OUTER APPLY
    t.xml_data.nodes('/del:DeliverMeterReading/del:Header') AS m(h)

如t= xml_data列的表。我尝试过一些select的变体，但不知道为什么它显示为null。我在外部应用路径和值提取选择中都包含了命名空间。

运行Server 2019。

XML node values where xml data has colon syntax也有类似的问题，marc_s和我回答了这个问题，但我只是调整了一下，试图在标签括号和标签内部(@用于标记内的值)之间获得值。

sql-server
xquery
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2021-10-29 07:40:45

最小错误-在.value()中，do not包括前面的'/'：

代码语言：javascript
复制
WITH XMLNAMESPACES(N'http://schemas.fortum.com/amm/delivermeterreading' AS del)
SELECT
    t.file_name, t.file_created_time received_timestamp,
    h.value(N'(del:MessageId)[1]', 'varchar(40)')
FROM
    load.t t
OUTER APPLY
    t.xml_data.nodes('/del:DeliverMeterReading/del:Header') AS m(h)
票数 1
EN

Stack Overflow用户

发布于 2021-10-29 10:36:31

如前所述，您有一个额外的/。

可能更容易使用DEFAULT命名空间。

代码语言：javascript
复制
WITH XMLNAMESPACES(DEFAULT N'http://schemas.fortum.com/amm/delivermeterreading')
SELECT
    t.file_name, t.file_created_time received_timestamp,
    h.value(N'(MessageId/text())[1]', 'varchar(40)')
FROM
    load.t t
OUTER APPLY
    t.xml_data.nodes('/DeliverMeterReading/Header') AS m(h)
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/69764541

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

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

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

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

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