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?

You might want to edit your posting to mention that you're using Oracle as your db server - might help. — marc_s, Mar 31 '09 at 14:55

gbn · Answer 1 · 2009-03-31T04:42:52.140

2

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

edited Mar 31 '09 at 04:42

answered Mar 30 '09 at 14:10

gbn

422,506
82
585
676

@ThinkJet: tags were updated after this answer. http://stackoverflow.com/posts/697256/revisions I answered for the information at the time – gbn Mar 22 '13 at 13:15
1

Ok, sorry for inconvience. I just missed comment for question at top of the page. – ThinkJet Mar 23 '13 at 13:02
@ThinkJet: no problems, happens often :-) – gbn Mar 23 '13 at 16:53

score 2 · Answer 2 · answered Mar 31 '09 at 05:06

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

score 0 · Answer 3 · edited Feb 04 '13 at 18:15

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

score 0 · Answer 4 · answered Mar 30 '09 at 13:49

0

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

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

answered Mar 30 '09 at 13:49

Matt Grande

11,964
6
62
89

Search XML Column in SQL

4 Answers4

Linked