在db2(sql/xml)中使用xmlquery从xml中选择元素

niwlg2el 于 2021-07-29 发布在 Java

关注(0)|答案(3)|浏览(403)

我在db2中创建了如下表：

create table xml_file(data xml not null)

这就是xml的确切结构：

<?xml version="1.0" encoding="UTF-8" ?>
     <student id="20140021">
          <name>Tom</name>
          <surname>Johnson</surname>
          <birth_date>"05/11/1995"</birth_date>
          <birth_place>"Miami"</birth_place>
          <points>9.45</points>
     </student>

我要为所有名为本、出生地为芝加哥的学生选择id、姓名、姓氏和分数。
我写了这样的东西：

select xmlquery('$DATA/student/data(@id)') as ID,
       xmlquery('$DATA/student/name/text()') as NAME,
       xmlquery('$DATA/student/surname/text()') as SURNAME,
       xmlquery('$DATA/student/points/text()') as POINTS
from xml_file
where xmlexists('$DATA/student[birth_place = "Chicago"]')
and xmlexists('$DATA/student[name = "Ben"]');

我得到的只是一条消息：“获取0条记录，显示0条记录”（这是在ibmdatastudio中）。
有人能告诉我我做错了什么吗？

sql xml db2 xpath xquery

来源：https://stackoverflow.com/questions/62292468/selecting-element-from-xml-using-xmlquery-in-db2-sql-xml

3条答案

按热度按时间

rkue9o1l1#

试着换两个 where xmlexists s与1（这与问题中的示例xml一起使用，而不是与代码一起使用）：

where xmlexists('$DATA//student[birth_place/text()['Miami']][name/text()["Tom"]]');

或者，需要两个：

where xmlexists('$DATA/student[birth_place/text()['Miami']]')
and xmlexists('$DATA/student[name/text()["Tom"]]');

看看这两种方法是否有效。

赞(0）回复(0）举报 2021-07-29

ybzsozfc2#

birth place元素包含双引号，这会导致xpath计算错误。为避免此情况，请更换 where xmlexists('$DATA/student[birth_place = "Chicago"]') 使用以下xpath表达式之一：
xpath 1.0友好型：

where xmlexists('$DATA/student/birth_place[substring(.,2,string-length(/student/birth_place)-2)="Chicago"]')

xpath 2.0友好：

where xmlexists('$DATA/student/birth_place[translate(.,codepoints-to-string(34),"")="Chicago"]')

用于测试示例数据的xpath:

/student/birth_place[substring(.,2,string-length(/student/birth_place)-2)="Miami"]
/student/birth_place[translate(.,codepoints-to-string(34),"")="Miami"]

赞(0）回复(0）举报 2021-07-29

ssm49v7z3#

试试这个：

/*
WITH xml_file (data) AS 
(
VALUES
XMLPARSE
(DOCUMENT '<?xml version="1.0" encoding="UTF-8" ?>
     <student id="20140021">
          <name>Tom</name>
          <surname>Johnson</surname>
          <birth_date>"05/11/1995"</birth_date>
          <birth_place>"Miami"</birth_place>
          <points>9.45</points>
     </student>'
)
)

* /

SELECT X.*
FROM 
  xml_file V
, XMLTABLE
  ('$doc/student' PASSING V.data AS "doc"
   COLUMNS
     ID      INT         PATH '@id'
   , NAME    VARCHAR(20) PATH 'name'
   , SURNAME VARCHAR(20) PATH 'surname'
   , POINTS  DEC(5, 2)   PATH 'points'
  ) X
WHERE XMLEXISTS('$doc/student[birth_place = """Miami""" and name = "Tom"]' PASSING V.data AS "doc");

赞(0）回复(0）举报 2021-07-29

我来回答

在db2(sql/xml)中使用xmlquery从xml中选择元素

3条答案

相关问题

热门标签

最新问答