How to request XML stored as text?

Question

I have a table in a SQL Server 2005 database that records purchases like this:

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

The request and response fields do store XML. I cannot change the data type in XML. I need to draw a query that will receive data from two text-as-XML fields in addition to the data that is in the table itself.

I'm not sure where to start. Most of my searches come back with LINQ-to-SQL questions, and the SQLXML results that I get do not seem to be able to process datasets. Where should I focus my search?

Answer 1

CAST ( AS XML), XML, ( , xml methods ).

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

XML , , , XML- (XmlDocument, XPathDocument, Linq 2 xml), .

Answer 2

" ", :

CAST(Request AS XML)

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

CAST( [Request] AS XML ), XML .

Answer 5

SQL- ( 2005 ), .NET, XML