FOR XML vs. ADO

Hi,
Not sure if this is the best newsgroup to ask this so let me know if there
is a better one. I am in the process of investigating converting a componen
t
which uses ADO recordsets to return data from a DB, to making use of the FOR
XML statement. When using recordsets the default date format which is outpu
t
is DD/MM/YYYY hh:mm:ss. However, when using FOR XML the output format is
YYYY-MM-DDThh:mm:ss. This is a bit of pain to be honest since the formats
need to be the same to ensure backwards compatibility. What are the reasons
for the differences? What would be the best way to perform the formatting o
f
the FOR XML output, taking into account that performance is paramount and
that any SQL conversions would have to be propogated to all of the existing
SQL stored procs/views?
Thanks.> When using recordsets the default date format which is output
> is DD/MM/YYYY hh:mm:ss.
That is not correct. When the data leaves SQL Server, it has no format. It i
s the client application
that formats the data. Your assumption of the above format is probably becau
se you had a regional
setting on the machine where you ran the client app that formatted the data
in such way.
For XML, it is a different story, as XML is not binary data (which datetime
is). So a format had to
be chosen, and the most reasonable choice would be the international standar
d for datetime
formatting, ISO 8601, which I believe also is common in other XML implementa
tions. I do not
recommend changing the formatting in your XML document as that would divert
from accepted standards.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:139B3299-FF59-4F4D-9D6E-CD502F4DDD70@.microsoft.com...
> Hi,
> Not sure if this is the best newsgroup to ask this so let me know if there
> is a better one. I am in the process of investigating converting a compon
ent
> which uses ADO recordsets to return data from a DB, to making use of the F
OR
> XML statement. When using recordsets the default date format which is out
put
> is DD/MM/YYYY hh:mm:ss. However, when using FOR XML the output format is
> YYYY-MM-DDThh:mm:ss. This is a bit of pain to be honest since the formats
> need to be the same to ensure backwards compatibility. What are the reaso
ns
> for the differences? What would be the best way to perform the formatting
of
> the FOR XML output, taking into account that performance is paramount and
> that any SQL conversions would have to be propogated to all of the existin
g
> SQL stored procs/views?
> Thanks.|||Regardless of what format the data has when it leaves SQL Server ADO formats
the date into the DD/MM/YYYY hh:mm:ss format by default, on our machines. B
y
using FOR XML I can speed up the component by approx 10% but the format must
remain the same. If this means breaking an accepted standard then so be it,
if a different format was returned it would essentially mean that every
single object within the business logic tier would have to checked and
altered to ensure that the new format is supported.
So taking the above into account do you have any suggestions for the second
question - What would be the best way to perform the formatting of the FOR
XML output, taking into account that performance is paramount and that any
SQL conversions would have to be propogated to all of the existing SQL store
d
procs/views?
"Tibor Karaszi" wrote:

> That is not correct. When the data leaves SQL Server, it has no format. It
is the client application
> that formats the data. Your assumption of the above format is probably bec
ause you had a regional
> setting on the machine where you ran the client app that formatted the dat
a in such way.
> For XML, it is a different story, as XML is not binary data (which datetim
e is). So a format had to
> be chosen, and the most reasonable choice would be the international stand
ard for datetime
> formatting, ISO 8601, which I believe also is common in other XML implemen
tations. I do not
> recommend changing the formatting in your XML document as that would diver
t from accepted standards.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Lee" <Lee@.discussions.microsoft.com> wrote in message
> news:139B3299-FF59-4F4D-9D6E-CD502F4DDD70@.microsoft.com...
>|||Why would you think that datetime values need to be formatted at all - other
than for presentation purposes?
You could convert datetime values to character data and format it but that
would just break the domain. I.e. you'd have to convert it back in order to
use it programatically.
What exactly are you trying to achieve?
ML
http://milambda.blogspot.com/|||Lee (Lee@.discussions.microsoft.com) writes:
> Regardless of what format the data has when it leaves SQL Server ADO
> formats the date into the DD/MM/YYYY hh:mm:ss format by default, on our
> machines. By using FOR XML I can speed up the component by approx 10%
> but the format must remain the same. If this means breaking an accepted
> standard then so be it, if a different format was returned it would
> essentially mean that every single object within the business logic tier
> would have to checked and altered to ensure that the new format is
> supported.
> So taking the above into account do you have any suggestions for the
> second question - What would be the best way to perform the formatting
> of the FOR XML output, taking into account that performance is paramount
> and that any SQL conversions would have to be propogated to all of the
> existing SQL stored procs/views?
You can use the convert function in a query force a certain date format,
look up "CAST and CONVERT" in Books Online. But it is an extremely poor
idea to do, because that means you are shoving a date format down the
throat of your users. The likelihood that I will use your is likely to
be miniscule, but I want to see dates formatted as YYYY-MM-DD.
So for a good implementation, you should recevie the XML document as you
do now, and then convert the date client-side according to the regional
settings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||By no means is this an ideal scenario, I understand that. The framework I a
m
working with is a number of years old and I fear that the business objects
expect a certain date format which means that if this new date format was
used everything would go boom.
The only thing I was trying to acheive was a small performance increase
without having to change the code in too many components which means that I
would have to maintain exactly the same input and output. FOR XML provides
a
nice performance boost but the output is not exactly the same hence risking
the breaking of other components.
"ML" wrote:

> Why would you think that datetime values need to be formatted at all - oth
er
> than for presentation purposes?
> You could convert datetime values to character data and format it but that
> would just break the domain. I.e. you'd have to convert it back in order t
o
> use it programatically.
> What exactly are you trying to achieve?
>
> ML
> --
> http://milambda.blogspot.com/|||Maybe I didnt explain the situation correctly. This has nothing to do with
the presentation or what the user sees. This is to do with the format of
date that the business objects depend on to perform the required processing.
If these existing components suddenly get a new date format then it would
bring everything tumbline down hence why it is imperative that the output
from the data access component remains the same as to ensure full
compatibility.
I get the impression from the various replies that what I want is not
possible without making alterations to several other pieces of code.
"Erland Sommarskog" wrote:

> Lee (Lee@.discussions.microsoft.com) writes:
> You can use the convert function in a query force a certain date format,
> look up "CAST and CONVERT" in Books Online. But it is an extremely poor
> idea to do, because that means you are shoving a date format down the
> throat of your users. The likelihood that I will use your is likely to
> be miniscule, but I want to see dates formatted as YYYY-MM-DD.
> So for a good implementation, you should recevie the XML document as you
> do now, and then convert the date client-side according to the regional
> settings.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||> I get the impression from the various replies that what I want is not
> possible without making alterations to several other pieces of code.
To the best of my knowledge, there's no setting for this. The only option I
can think of would be to
indeed have CONVERT around the datetime column in every SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:6AD5B648-3CCF-4368-A4C8-741D28F253E8@.microsoft.com...
> Maybe I didnt explain the situation correctly. This has nothing to do wit
h
> the presentation or what the user sees. This is to do with the format of
> date that the business objects depend on to perform the required processin
g.
> If these existing components suddenly get a new date format then it would
> bring everything tumbline down hence why it is imperative that the output
> from the data access component remains the same as to ensure full
> compatibility.
> I get the impression from the various replies that what I want is not
> possible without making alterations to several other pieces of code.
> "Erland Sommarskog" wrote:
>|||Lee (Lee@.discussions.microsoft.com) writes:
> Maybe I didnt explain the situation correctly. This has nothing to do
> with the presentation or what the user sees. This is to do with the
> format of date that the business objects depend on to perform the
> required processing.
A horrible application design, in my opinion. If someone would change
the regional settings to US English, your business objects would choke -
or even worse: misinterpret the dates.

> If these existing components suddenly get a new date format then it would
> bring everything tumbline down hence why it is imperative that the output
> from the data access component remains the same as to ensure full
> compatibility.
> I get the impression from the various replies that what I want is not
> possible without making alterations to several other pieces of code.
Since you were to introduce XML into the pot, it appears that you are
into changing code anyway. You can get back dates in various formats
with XML, if you use the convert() function to format them as strings
in SQL Server.
But if you add XML, you will have to change the code client-side as
well, so I don't really see the problem with converting dates from
the XML documents to the regional settings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

for xml explicit problem with a self join

What is the syntax for converting a simple sql statement like this:
SELECT
PortalDirectory.PortalDirectoryUserID,
PD1.AttributeValue
FROM
PortalDirectory
INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID =
PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '12345')
... into a FOR XML EXPLICIT statement?
This is what i've tried but it doesn't work, i always get this error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'PortalDirectory' does not match with a table name or
alias name used in the query.
SELECT
1 AS TAG,
NULL AS PARENT,
PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
NULL AS [PD1!2!Value]
UNION ALL SELECT
2 AS TAG,
1 AS PARENT,
NULL AS [PortalDirectory!1!ID],
PD1.AttributeValue AS [PD1!2!Value]
FROM
PortalDirectory
INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID =
PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '11351')
FOR XML EXPLICIT
there must be something pretty basic i'm missing, as i've looked everywhere
and noone mentions what to do when you have 2 tables that are really the
same one (if there is a special name for what i'm doing i can't think of
it!!)
Thanks
Paul
Every select statement in a UNION ALL needs its own from clause. Best is to
first write the query without the FOR XML aspects.
For some more complex (but still small) FOR XML explicit queries, see the
FOR XML in SQLServer 2005 whitepaper at
http://msdn.microsoft.com/XML/Buildi...forxml2k5.asp.
Best regards
Michael
"Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
news:2p16plFfhntkU1@.uni-berlin.de...
> What is the syntax for converting a simple sql statement like this:
> SELECT
> PortalDirectory.PortalDirectoryUserID,
> PD1.AttributeValue
> FROM
> PortalDirectory
> INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID
> =
> PD1.PortalDirectoryUserID
> WHERE (PortalDirectory.AttributeValue = '12345')
> ... into a FOR XML EXPLICIT statement?
> This is what i've tried but it doesn't work, i always get this error:
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'PortalDirectory' does not match with a table name or
> alias name used in the query.
> SELECT
> 1 AS TAG,
> NULL AS PARENT,
> PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
> NULL AS [PD1!2!Value]
> UNION ALL SELECT
> 2 AS TAG,
> 1 AS PARENT,
> NULL AS [PortalDirectory!1!ID],
> PD1.AttributeValue AS [PD1!2!Value]
> FROM
> PortalDirectory
> INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID =
> PD1.PortalDirectoryUserID
> WHERE (PortalDirectory.AttributeValue = '11351')
> FOR XML EXPLICIT
>
> there must be something pretty basic i'm missing, as i've looked
> everywhere
> and noone mentions what to do when you have 2 tables that are really the
> same one (if there is a special name for what i'm doing i can't think of
> it!!)
>
> Thanks
> Paul
>
|||Doh, i can't believe i missed that about the FROM clause. Thank you.
Nevertheless, it still doesnt work properly.
So i now have this
SELECT
1 AS TAG,
NULL AS PARENT,
PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
NULL AS [PD1!2!Value]
FROM
PortalDirectory INNER JOIN PortalDirectory PD1
ON PortalDirectory.PortalDirectoryUserID = PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '11351')
UNION ALL SELECT
2 AS TAG,
1 AS PARENT,
NULL AS [PortalDirectory!1!ID],
PD1.AttributeValue AS [PD1!2!Value]
FROM
PortalDirectory INNER JOIN PortalDirectory PD1
ON PortalDirectory.PortalDirectoryUserID = PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '11351')
--FOR XML EXPLICT
Looking at the universal table that this produces, i get duplicate rows for
the first table one for each actual result row (which meansi i get lots of
<PortalDirectory ID="14"/><PortalDirectory ID="14"/>... )
Now i could change the first SELECT to be SELECT DISTINCT (and it does work
fine) but this can't be the way to do it surely - it just feels like a
workaround bad code. Adding in ordering doesnt make a difference - there
are simply too many rows being put into the universal table.
Michael Rys [MSFT] wrote:
> Every select statement in a UNION ALL needs its own from clause. Best
> is to first write the query without the FOR XML aspects.
> For some more complex (but still small) FOR XML explicit queries, see
> the FOR XML in SQLServer 2005 whitepaper at
>
http://msdn.microsoft.com/XML/Buildi...forxml2k5.asp.[vbcol=seagreen]
> Best regards
> Michael
> "Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
> news:2p16plFfhntkU1@.uni-berlin.de...
|||A quick addition to my previous post - i could also do SELECT TOP 1 in my
first SELECT statement which would also work and be better than SELECT
DISTINCT however i still have the feeling that i'm doing something
fundamentally wrong or there is a better way.
Paul wrote:
> Doh, i can't believe i missed that about the FROM clause. Thank you.
> Nevertheless, it still doesnt work properly.
> So i now have this
> SELECT
> 1 AS TAG,
> NULL AS PARENT,
> PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
> NULL AS [PD1!2!Value]
> FROM
> PortalDirectory INNER JOIN PortalDirectory PD1
> ON PortalDirectory.PortalDirectoryUserID = PD1.PortalDirectoryUserID
> WHERE (PortalDirectory.AttributeValue = '11351')
> UNION ALL SELECT
> 2 AS TAG,
> 1 AS PARENT,
> NULL AS [PortalDirectory!1!ID],
> PD1.AttributeValue AS [PD1!2!Value]
> FROM
> PortalDirectory INNER JOIN PortalDirectory PD1
> ON PortalDirectory.PortalDirectoryUserID =
> PD1.PortalDirectoryUserID WHERE (PortalDirectory.AttributeValue =
> '11351') --FOR XML EXPLICT
> Looking at the universal table that this produces, i get duplicate
> rows for the first table one for each actual result row (which meansi
> i get lots of <PortalDirectory ID="14"/><PortalDirectory ID="14"/>...
> )
> Now i could change the first SELECT to be SELECT DISTINCT (and it
> does work fine) but this can't be the way to do it surely - it just
> feels like a workaround bad code. Adding in ordering doesnt make a
> difference - there are simply too many rows being put into the
> universal table.
>
> Michael Rys [MSFT] wrote:
>
http://msdn.microsoft.com/XML/Buildi...forxml2k5.asp.[vbcol=seagreen]

FOR XML AUTO, ELEMENTS Problem

I have a column ('ProblemResolution') in a table ('Incident') that holds plain text. I am doing a query against that column and converting the results to XML as follows:

Select ProblemResolution From Incident Where RowID = 2 FOR XML AUTO, ELEMENTS

The problem is the XML that is being generated. It appears the XML that is generated is illegal (in some cases) because if I save the resulting XML in a text file and load into Internet Explorer, IE generates errors.

Here is the plain text (actually part of it - enough to demo the problem) as stored in the column. The quotes are not stored.

"8/11/2006 dabonder -
Carol –

Thanks for the detail. I looked at the 6060 transaction and it was as you thought – these accounts are not set up in the .|
If the corresponding project account to 6060 would never be used in a time sheet or expense report then you would not need to have a.
I haven't had time to clarify this. If you want to discuss when you get time I would be happy to.

-D Abonder

D Abonder
Director of Consulting
Some Company
www.SomeCompany.com

Email: dgonder@.somecompany.com
Phone: 123-555-3450"

Here is the generated (illegal) XML:

<Incident><ProblemResolution>8/11/2006 dabonder -&#x0D;
&#x0D;
Carol –&#x0D;
&#x0D;
Thanks for the detail. I looked at the 6060 transaction and it was as you thought – these accounts are not set up in the .&#x0D;
&#x0D;
If the corresponding project account to 6060 would never be used in a time sheet or expense report then you would not need to have a.&#x0D;
&#x0D;
I haven&apos;t had time to clarify this. If you want to discuss when you get time I would be happy to.&#x0D;
&#x0D;
-D Abonder&#x0D;
&#x0D;
D Abonder&#x0D;
Director of Consulting&#x0D;
Some Company&#x0D;
www.SomeCompany.com&#x0D;
&#x0D;
Email: dgonder@.somecompany.com&#x0D;
Phone: 123-555-3450</ProblemResolution></Incident>

The problem is that the document contains invalid U+0000 characters that are not allowed in XML. FOR XML does not mark them as errors but outputs them anyway. You should clean your data in the table or when running your FOR XML expression or before passing the XML to the parser and remove the U+0000 code point or replace the &#x0D; with the zero-length string.

Best regards

Michael