使用SQL Server"FOR XML":将结果数据类型转换为Text/varchar/string是什么? [英] 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 -.-

我的目标是通过使用"FOR XML"语句进行查询从SQL Server接收XML，并将该XML作为字符串/流以转发到XSLT Transformer并将结果显示为HTML.

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.

可怜的是我应该使用的Web服务器环境:IBM Domino 8.5.2，通常使用Lotus Script和Lotus Notes数据库来生成网站.

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.

但是，当谈论大型数据库(例如我们的网站点击统计信息)时，Notes数据库的性能已经达到了死胡同，该数据库将数百万的点击数存储为数据集.因此，我们决定迁移到可以更快提供自定义过滤结果的mssql2008服务器.

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.

我实际上确实设法建立了与SQL Server的连接，提交了一个查询，并将结果显示为xml.但现在? :)似乎SQL Server提供了"for xml"查询的结果，作为一种自己的二进制编码字符的奇怪数据类型，当打印它们或将其保存到文件时没有意义.而且Lotus Script(几乎是VBScript)似乎无法处理这种xml数据类型.

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.

我尽一切可能找到结果-Lotus内置ODBC类，通过OleDB进行LCConnection，通过OleDB进行ADO连接...但是每次尝试都以一堆无法读取的数据结束.

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.

我可以想象在Domino和SQL Server之间建立一个Web服务，将数据作为字符串准备好用于Domino脚本，但这是我想要避免的不必要的开销.

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.

您能否在sql语句中告诉您希望结果xml成为哪种数据类型?就像字段的cast()/convert()一样，但是对于整个xml结果呢?这样我就可以将结果读取为text/varchar字段?

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，克里斯

推荐答案

我知道我以前做过类似的事情，而我想这就是你要做的事情:

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

这将吐出您以XML生成的任何内容作为Lotus Notes可以接受的文本数据.

That will spit out whatever you've generated as XML as text data that Lotus Notes may well accept.

如果需要以其他样式生成XML，则可以将FOR XML PATH('')更改为FOR XML AUTO.

You could change FOR XML PATH('') to FOR XML AUTO if you need the XML to be generated in another style.

这篇关于使用SQL Server"FOR XML":将结果数据类型转换为Text/varchar/string是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！