Querying XML dumps in SQL

Posted on 2009-04-22
Last Modified: 2012-05-06
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?
Question by:Thom Gann
    LVL 11

    Accepted Solution

    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

    Author Closing Comment

    by:Thom Gann
    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!!!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now