Querying XML dumps in SQL

I would like to know if there is a function that will search specific arguments buried in an XLM dump within the SQL table. I tried: select * from pcr where DATA LIKE '%682%' and I get the error: Msg 8116, Level 16, State 1, Line 1 Argument data type xml is invalid for argument 1 of like function.

Is there a function that will act the same as LIKE but work within XML?
Thom GannIT Systems ManagerAsked:
Who is Participating?
 
aaronakinCommented:
I'm not an expert on XQuery, but if you want to search the text value of a particular node in the XML, you can use something like this...

SELECT *
  FROM pcr
  WHERE DATA.exists('/RootNode/ChildNode1/ChildNode1a/text()[contains(.,"682")]') = 1
  --WHERE DATA.exists('/RootNode/ChildNode1/ChildNode1a/text()[contains(.,sql:variable("@Var"))]') = 1  --If you're using a variable:  DECLARE @Var VARCHAR(10)

If you want to search the entire XML contents, including the nodes, I think you might have to convert it to varchar(max) first and then use a LIKE statement like this...

SELECT *
  FROM pcr
  WHERE CAST(DATA AS VARCHAR(MAX)) LIKE '%682%'
0
 
Thom GannIT Systems ManagerAuthor Commented:
PERFECT!! Coverting the data to varchar(max) allowed me to search, then I narrowed the search to the include the specific XML tags I needed and I found the data!! THANK YOU!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.