CODEWITHSUNDEEP
sqlsql-serverxml
JMP
JMP

Reputation: 7844

How do I query XML stored as text?

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?

Upvotes: 3

Views: 5223

Answers (5)

Byron Whitlock
Byron Whitlock

Reputation: 53901

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

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

Upvotes: 2

Peter M
Peter M

Reputation: 7493

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

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294387

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.

Upvotes: 4

David
David

Reputation: 34563

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

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171491

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

CAST(Request AS XML)

Upvotes: 2

Related Questions