Search XML Column in SQL

Question

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

Answer 1

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

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

Answer 2

not so easy :) i want to have only row that provides my conditions. your query returns me all of the xml document. İ only want the row that contains UnitID=2

here my result should be UnitID="2" Name="Jerry" SName="Potter"/> row.

Answer 3

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

Answer 4

thanks but...it doesn't work... my table name is CourseXML where did you set the XML name? İ couldn't find Table name in your query... Also i'm using Oracle does it differ from SQL server.

Answer 5

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

Answer 6

i tried but it didn't work yet here is my code sample... may be problem is , i am working on Oracle 10g does it make differ from SQL Server 2005

  SELECT    y.item.value('@DOGUM_YERI', 'varchar(100)') AS YER
  FROM KURS_XML CROSS APPLY XML_DATA.nodes('/NUFUS') AS y(item)
  WHERE    y.item.value('@DOGUM_YERI', 'varchar(100)') = "BİRECİK"

it gives me an error that "SQL command not properly ended"

Answer 7

Being the structure, and using Full Text Search.

  
    Actividad
    Actividad 2
  
  
    Cliente
    Alpina
  

 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 recomend using atributes on the XML, because full text search cannot be performed on atributes (no matter what the SQL 2008 R2 Docs SAY).

Please Refer to this