如何从 xml 中获取 CDATA 值 [英] How can i get CDATA value from xml

问题描述

感谢点击，

1. 我需要 CDATA 中的值

1. I need value in CDATA

如果我想找一个叫玛丽亚，生日是2012-03-12的人我如何找到它??

If I want to find someone whose name is Maria and her birthday is 2012-03-12 How do I find it??

SELECT t.doc.extract('/RESPONSE/INFO/STU_NAME/text()').getStringVal()
"stu"
FROM table t
WHERE  t.doc.extract('/RESPONSE/INFO/STU_NAME/text()').getStringVal()
LIKE '%M%'

我试过了，但你看到下面是结果

I done try it, but you see below is the result

<![CDATA[ Maria ]]>

我不需要 <![CDATA[]]>

I don't need <![CDATA[]]>

// XML
<STUDENT>
<INFO>
  <STU_NAME><![CDATA[ Maria ]]></STU_NAME>
  <STU_WARN><![CDATA[ one ]]></STU_WARN>
  <BIRTHDAY><![CDATA[ 2012-03-12 ]]></BRITHDAY>
</INFO>
<INFO>
  <STU_NAME><![CDATA[ Kevin) ]]></STU_NAME>
  <STU_WARN><![CDATA[ one ]]></STU_WARN>
  <BIRTHDAY><![CDATA[ 2010-07-15 ]]></BRITHDAY>
</INFO>

推荐答案

提取功能已被弃用很长时间(至少从 11gR2 - 请参阅该文档中的注释).

The extract function has been deprecated for a long time (at least since 11gR2 - see the note in that documentation).

如果您有多个值并且可能想要查看多个值，您可以使用 XMLTable，它可以消除 CDATA 噪音(但可能需要修剪，因为您的值中有空格):

If you have multiple values and might want to see more than one you could use XMLTable, which removes the CDATA noise (but might need a trim as you have spaces in the values):

select x.stu_name, x.birthday,
  trim(stu_name) as stu_name2, to_char(x.birthday,'YYYY-MM-DD') as birthday2
from your_table t
cross join xmltable ('/RESPONSE/INFO' passing t.doc
  columns
    stu_name varchar2(30) path 'STU_NAME',
    birthday date path 'BIRTHDAY'
) x
where x.stu_name like '%M%'

STU_NAME BIRTHDAY  STU_NAME2 BIRTHDAY2
-------- --------- --------- ---------
 Maria   12-MAR-12 Maria     2012-03-12

如果您的目标是单个值，您还可以使用 xmlquery，它更接近您的提取:

If you're targeting a single value you could also use xmlquery, which is closer to your extract:

select regexp_replace(
  xmlquery('/RESPONSE/INFO[contains(BIRTHDAY, "2012-03-12")]/STU_NAME/text()'
    passing doc
    returning content),
  '<!\[CDATA\[ *(.*?) *\]\]>', '\1') as stu_name
from your_table t

STU_NAME
--------
Maria

这是我在节点中查找您想要的生日作为文本，并获得匹配的名称；但由于它仍然具有 CDATA，它与您拥有的大致相同.所以，我使用了一个正则表达式来去除 CDATA 部分，不过如果你关心性能，你也可以使用 substr/instr.

Here's I've looked for the birthday you wanted as text within a node, and got the matching name; but as that still has the CDATA it is roughly the same as what you had. So, I've used a regular expression to strip the CDATA part, though you could also use substr/instr if performance is a concern.

db<>fiddle

这篇关于如何从 xml 中获取 CDATA 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！