• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

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?
0
Thom Gann
Asked:
Thom Gann
1 Solution
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now