How do I query XML stored as text?

sql, sql-server, xml

Solution

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.

Problem

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?

Original source