在SQL中搜索XML列 [英] Search XML Column in SQL

问题描述

İ有一个XML文档可针对Oracle数据库中的记录进行存储.

İ have an XML document to store against records in an Oracle Database.

表CourseXML将包含:

The table CourseXML will contain:

Record_Number  int
XML_Type       int
XMLDoc         clob
...etc

İ想通过XML标签在XMLDoc列中进行搜索. XML文档具有这样的XML模式:

İ would like to make a search in XMLDoc column by XML tags. The XML document has an XML Schema like this:

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

İ要在UnitID ="2"中进行搜索，而我只希望Jerry的xml行.我该如何进行选择语句查询才能获取该xml行?

İ want to make search in UnitID="2" and i only want Jerry's xml row. How I have to make a select statement query to get that xml row?

推荐答案

您有很多获取它的方法. "gbn"显示了一种方式-另外两种在这里.

You have plenty of ways of getting it. "gbn" showed one way - two other are here.

如果您想要整个行"(我假设您会将这些内容放入标签中)，请尝试以下操作:

If you want the whole "row" (I assumed you'll put these things into a tag), try this:

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

如果只需要标记中的名称"属性，请使用以下方法:

If you want just the "Name" attribute from the tag, use this:

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

如果您需要访问一大堆属性和/或子元素，请使用gbn的方法和"CROSS APPLY xmldoc.nodes(....)".

If you need to access a whole bunch of attributes and/or subelements, use gbn's approach with the "CROSS APPLY xmldoc.nodes(....)".

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

Enjoy! XML Support in SQL Server 2005 is really quite extensive and useful!

马克

这篇关于在SQL中搜索XML列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！