Showing posts with label definition. Show all posts
Showing posts with label definition. Show all posts

Tuesday, February 14, 2012

Changing document type definition when using FOR XML AUTO

Hi
When using FOR XML AUTO in sql server is there a way to change the
document typ definition (<?xml version="1.0"?> ). What I wanna do is
add the encoding property so it instead of the above reads <?xml
version="1.0" encoding="ISO-8859-1"?>.
The reason I wanna do this is because I have characters like "" and
"".
Or is this something I can handle in my .asp-page?
Thankfull for all suggestions!
/Christer
ASP code:
<%
Response.Buffer = true
'Response.ContentType = "text/xml"
Dim cn, rs,sQuery, cmd, xmldoc
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
Set cn = Server.CreateObject("ADODB.Connection")
cn.open CnString
Set xmldoc = Server.CreateObject("MSXML2.DomDocument")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
sQuery = "getDiaryXML"
cmd.CommandText = sQuery
cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText
Set cmd = Nothing
cn.Close
Set cn = Nothing
%>
<%=xmldoc.xml%>
***********************
SQL:
SELECT
YEAR(dtDate) AS strYear,
DATENAME(MONTH,dtDate) AS strMonth,
DAY(dtDate) AS strDay,
diaryID,
txtDiaryHeader,
txtDiaryIngress,
txtDiaryText,
id as photoID
FROM diary ORDER BY dtDate
FOR XML AUTOHi
I think the problem is that the encoding should be UTF-16 this may help
http://groups-beta.google.com/group...c?
hl=en
John
"Christer" wrote:

> Hi
> When using FOR XML AUTO in sql server is there a way to change the
> document typ definition (<?xml version="1.0"?> ). What I wanna do is
> add the encoding property so it instead of the above reads <?xml
> version="1.0" encoding="ISO-8859-1"?>.
> The reason I wanna do this is because I have characters like "?" and
> "?".
> Or is this something I can handle in my .asp-page?
> Thankfull for all suggestions!
> /Christer
> ASP code:
> <%
> Response.Buffer = true
> 'Response.ContentType = "text/xml"
> Dim cn, rs,sQuery, cmd, xmldoc
> Const adExecuteStream = &H00000400
> Const adCmdText = &H0001
> Set cn = Server.CreateObject("ADODB.Connection")
> cn.open CnString
> Set xmldoc = Server.CreateObject("MSXML2.DomDocument")
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = cn
> sQuery = "getDiaryXML"
> cmd.CommandText = sQuery
> cmd.Properties("xml root").Value = "root"
> cmd.Properties("Output Stream") = xmldoc
> cmd.Execute , , adExecuteStream + adCmdText
> Set cmd = Nothing
> cn.Close
> Set cn = Nothing
> %>
> <%=xmldoc.xml%>
> ***********************
> SQL:
> SELECT
> YEAR(dtDate) AS strYear,
> DATENAME(MONTH,dtDate) AS strMonth,
> DAY(dtDate) AS strDay,
> diaryID,
> txtDiaryHeader,
> txtDiaryIngress,
> txtDiaryText,
> id as photoID
> FROM diary ORDER BY dtDate
> FOR XML AUTO
>