How do I query XML stored as text?

Question

I have a table in a SQL Server 2005 database that logs purchases like so:

ID (PK, int, not null)
Request (text, null)
Response (text, null)
MerchantId (varchar(14), null)
Amount (money, null)

The Request and Response fields are really storing XML. I can't change the data type to XML. I need to draw a query that will get data out of the 2 text-as-XML fields in addition to data that is in the table itself.

I'm not sure where to start with this. Most of my searches come back with questions about LINQ-to-SQL, and the SQLXML results I get don't seem to be able to handle sets of data. Where should I be focusing my search?

Answer 1

You could cast the data on the fly, e.g.:

CAST(Request AS XML)

Answer 2

You can always CAST( [Request] AS XML ) and then use all of the regular XML functions to extract data from it.

Answer 3

SELECT request.VALUE(/xpath/query)
FROM
(
    SELECT 
       CAST(Request as XML) request,
       CAST(Response as XML) response

    FROM purchaseTbl
    WHERE ...
) tbl
WHERE ...

Answer 4

Use CAST(text AS XML) to obtain an XML typed column that can be manipulated at the server (use value, nodes and query xml methods on it).

SELECT ID, 
  CAST(Request AS XML) As RequestXml,
  CAST(Request AS XML) As ResponsetXml,
  MerchantId,
  Amount
FROM <table>

If you only need the XML in the client, then you can just return the text and then use whatever is your client side XML technology of choice (XmlDocument, XPathDocument, Linq 2 xml) they all allow you to cosntruct a fragment from a string.

Answer 5

If you are using SQL server (2005 onwards), one possibility could be to write a user defined function in a .Net language that parses out the XML from a field and returns that data as part of teh query