在 SQL 中搜索 XML 列

Question

我可以帮您进行翻译，以下是关于IT技术的内容：

我有一个XML文档需要存储到Oracle数据库的记录中。

表CourseXML将包含以下内容：

Record_Number  int
XML_Type       int
XMLDoc         clob
...etc

我希望能够通过XML标签在XMLDoc列中进行搜索。XML文档具有以下XML模式：

<root>
  <UnitID="2" Name="Jerry" SName="Potter"/>
  <UnitID="3" Name="Jim" SName="Carelyn"/>
</root>

我想在UnitID="2"中搜索，并且只想要Jerry的xml行。我该如何编写查询语句以获取该xml行？

- enormous81

---

您可能希望编辑您的帖子，提到您正在使用 Oracle 作为您的数据库服务器 - 可能会有所帮助。 - marc_s

Answer 1

您可能需要微调节点才能精确匹配。

SELECT
    y.item.value('@UnitID', 'int') AS UnitID,
    y.item.value('@Name', 'varchar(100)') AS [Name],
    y.item.value('@SName', 'varchar(100)') AS [SName]
FROM
    <table>
    CROSS APPLY
    XMLDoc.nodes('/root') AS y(item)
WHERE
    y.item.value('@UnitID', 'int') = 2

编辑：更正代码使用表格，而不是XML本地变量

- gbn

---

@ThinkJet：此回答发布后，标签已更新。http://stackoverflow.com/posts/697256/revisions 我是根据当时的信息进行回答的。 - gbn

好的，抱歉给您带来不便。我只是错过了页面顶部的问题注释。 - ThinkJet

@ThinkJet：没问题，经常发生 :-) - gbn

Answer 2

您有很多获取它的方式。"gbn"展示了其中一种方式，这里还有两种方法。

如果您想要整个“行”（我假设您将这些内容放入一个标签中），请尝试以下方法：

select
    xmldoc.query('//node[@UnitID="2"]')
from
    xmltest

如果你只想获取 a 标签中的 "Name" 属性，请使用以下代码：

select
    xmldoc.value('(//node[@UnitID="2"]/@Name)[1]', 'varchar(20)')
from
    xmltest

如果您需要访问大量属性和/或子元素，请使用gbn的方法，即“CROSS APPLY xmldoc.nodes(....)”。

享受吧！SQL Server 2005中的XML支持非常广泛且实用！

马克

- marc_s

Answer 3

作为结构，使用全文搜索。

<Root>
  <Tags>
    <TagName>Actividad</TagName>
    <Valor>Actividad 2</Valor>
  </Tags>
  <Tags>
    <TagName>Cliente</TagName>
    <Valor>Alpina</Valor>
  </Tags>
</Root>

 select 
     Filename
 from 
 Files
 where 
 CONTAINS(Tags,'Actividad')  and
 CONTAINS(Tags,'Cliente')  and
 Tags.exist('//Tags/Valor/text()[contains(., "Actividad 1")]')  = 1 and
 Tags.exist('//Tags/Valor/text()[contains(., "ADV")]')  = 1

我不建议在XML上使用属性，因为无法对属性执行全文搜索（无论SQL 2008 R2文档如何说）。

请参考此链接

- Jaime Bula

Answer 4

SELECT * FROM CourseXML
WHERE XMLDoc = 'UnitID="2"'

就是这样吗？还是我理解错了什么？

- Matt Grande