Using SQL Server "FOR XML": Convert Result Datatype to Text/varchar/string whatever?
I'm struggling with this nearly one week now, and still haven't found any solution -.-
My goal is to receive an XML from a SQL Server by querying with the "FOR XML" statement, taking this XML as a string/stream to forward to a XSLT Transformer and presenting the result as HTML.
The poor thing about it is the webserver environment I should use: IBM Domino 8.5.2, which usually uses Lotus Script and Lotus Notes Databases to generate websites.
But now the performance of Notes databases have reached their dead end, when talking about big databases like our website hit statistics which store millions of hits as datasets. So we decided to migrate to an mssql2008 server that can provide custom filtered results more quickly.
I did actually manage to establish a connection to SQL Server, submit a query and have the result as xml. But ... now? :) It seems that SQL Server provides his "for xml"-queried results as a kind of own weird datatype of binary encoded chars that make no sense when printing them or saving to a file. And Lotus Script (pretty much VBScript) doesn't seem to have a possibility to handle this xml datatype.
I tried every way i could find getting the result - Lotus built in ODBC class, LCConnection via OleDB, ADO Connection via OleDB... but every try ends in a bunch of unreadable data.
I could imagine a webservice between domino and SQL Server, preparing the data as string for my domino script, but that's unnecessary overhead i'd like to avoid.
Can you tell in the sql statement what datatype you want the resulting xml to be? just like cast()/convert() for fields but for the whole xml result? So that i could just read the result as text/varchar field?
Thx, Chris
Answer
I knew I'd done something like this before, and I think this is what you're after:
SELECT CAST((SELECT [Columns] FROM [Tables] FOR XML PATH('')) AS VARCHAR(MAX)) AS XmlData
That will spit out whatever you've generated as XML as text data that Lotus Notes may well accept.
Edit: You could change FOR XML PATH('') to FOR XML AUTO if you need the XML to be generated in another style.