CODEWITHSUNDEEP
xmloracle-databaseselect
enormous81
enormous81

Reputation: 61

Search XML Column in SQL

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

The table CourseXML will contain:

Record_Number  int
XML_Type       int
XMLDoc         clob
...etc

İ 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>

İ 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?

Upvotes: 3

Views: 12408

Answers (4)

Jaime Bula
Jaime Bula

Reputation: 51

Being the structure, and using Full Text Search.

<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

I do not recommend using attributes on the XML, because full text search cannot be performed on attributes (no matter what the SQL 2008 R2 Docs say).

Please Refer to this

Upvotes: 0

marc_s
marc_s

Reputation: 754518

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

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

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

Marc

Upvotes: 2

gbn
gbn

Reputation: 432271

You may have to play with the nodes bit to get it exact.

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

Edit: corrected code to use table, not xml local variable

Upvotes: 2

Matt Grande
Matt Grande

Reputation: 12157

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

Isn't that it? Or am I misunderstanding something?

Upvotes: 0

Related Questions