MS SQL XML - FOR XML AUTO is inconsistent in MSSQL 2000 Ent but not MSSQL 7 Ent

Author

FOR XML AUTO is inconsistent in MSSQL 2000 Ent but not MSSQL 7 Ent

Rainabba

2005-07-18, 8:24 pm

I've inherited a older, but decently designed ASP app that utilizes SQLXML
through XML templates that contain SELECT queries and EXEC sp queries. All
works well with the old system where MSDE 7 was being used, but when I began
to try updating the app, I was stopped in my tracks after migrating the app
to a SQL 2000 db (service pack level doesn't seem to be a factor, but I've
tried RTM, SP3, and SP4. I know the problem exists is MSSQL and not SQLXML
because I can run the app from a given webserver and point the DSN to the
SQL7 db or the SQL2000 db and the trouble exists only when it's directed to
the SQL2000 db.

That said, the trouble is the any query run against the SQL2000 db using
'FOR XML AUTO, ELEMENTS' returns inconsistent results. I seem to get 3
versions of a result back. I'll list some shortened versions below after the
sample query. It will be a task, but I hope to update everything to explicit
mappings in the future, but in the meantime, I'm limited to running SQL7 and
that's no good. To clarify, when I say I get inconsistent results, I mean;
if i just keep refreshing the web request below, i get different documents
occasionally.

http://satdemo/XML/CustomerWelcome....9B-3F344C282531

I'd like to know if anyone has had this problem, can replicate the results,
provide a solution, or if any MS representatives can pick this up and verify
the problem or provide a solution..

Thank you,

Rainabba ( rainabba@ureach.com )

-----------------------------------------Sample
Query----------------------------------------------
<?xsl-serverstylesheet xslfile="CustomerWelcome.xsl" ?>
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Companyname>
<sql:query SessionID="">
select ? SessionId for xml auto
</sql:query>
<sql:query>
exec xmlCustomerWelcome ? for xml auto
</sql:query>
</Companyname>
</root>
----------------------------------------------------------------------------
-------------------------

---------------------------------------Sample
Results------------------------------------------------
------ Version 0 (expected and prefered) --------
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Companyname>
<StoredProc>
<SessionId>XXXX-XXXXXXX-XXXXXXXXXX-XXXXXXXXXX</SessionId>
</StoredProc>
<Company>
<SessionId>XXXX-XXXXXXX-XXXXXXXXXX-XXXXXXXXXX</SessionId>
<Name>0005-06-00 NGB Test Company</Name>
<FirstName>IAmThe</FirstName>
<LastName>PERSON</LastName>
<CompanyID>0</CompanyID>
<CoPAStatus>0</CoPAStatus>
<Project>
<Description>0005-06-00</Description>
<ProjectID>04</ProjectID>
<sponsor>0</sponsor>
<Person>
<PersonID>0</PersonID>
<ProjectAs>
<ProjectRole>0</ProjectRole>
<PERSONAgree>0</PERSONAgree>
<BPLIStatus>0</BPLIStatus>
</ProjectAs>
</Person>
</Project>
</Company>
</Companyname>
</root>

----Version 1 -----
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Companyname>
<StoredProc>
<SessionId>XXXX-XXXXXXX-XXXXXXXXXX-XXXXXXXXXX</SessionId>
</StoredProc>
<StoredProc>
<SessionId>XXXX-XXXXXXX-XXXXXXXXXX-XXXXXXXXXX</SessionId>
<Name>0005-06-00 NGB Test Company</Name>
<FirstName>IAmThe</FirstName>
<LastName>PERSON</LastName>
<Description>0005-06-00</Description>
<CompanyID>0</CompanyID>
<ProjectID>04</ProjectID>
<PersonID>0</PersonID>
<sponsor>0</sponsor>
<ProjectRole>0</ProjectRole>
<CoPAStatus>0</CoPAStatus>
<PERSONAgree>0</PERSONAgree>
<BPLIStatus>0</BPLIStatus>
</StoredProc>
</Companyname>
</root>
-----------------------
----------------------------------------------------------------------------
-------------------

Michael Rys [MSFT]

2005-07-20, 8:24 pm

SQLXML is not supported against SQL Server 7. Are you using the SQL Server 7
based SQLXML technology preview?

Best regards
Michael

"Rainabba" <rainabba@ureach.com> wrote in message
news:uTCyQH%23iFHA.3540@TK2MSFTNGP14.phx.gbl...
> I've inherited a older, but decently designed ASP app that utilizes SQLXML
> through XML templates that contain SELECT queries and EXEC sp queries. All
> works well with the old system where MSDE 7 was being used, but when I
> began
> to try updating the app, I was stopped in my tracks after migrating the
> app
> to a SQL 2000 db (service pack level doesn't seem to be a factor, but I've
> tried RTM, SP3, and SP4. I know the problem exists is MSSQL and not SQLXML
> because I can run the app from a given webserver and point the DSN to the
> SQL7 db or the SQL2000 db and the trouble exists only when it's directed
> to
> the SQL2000 db.
>
> That said, the trouble is the any query run against the SQL2000 db using
> 'FOR XML AUTO, ELEMENTS' returns inconsistent results. I seem to get 3
> versions of a result back. I'll list some shortened versions below after
> the
> sample query. It will be a task, but I hope to update everything to
> explicit
> mappings in the future, but in the meantime, I'm limited to running SQL7
> and
> that's no good. To clarify, when I say I get inconsistent results, I mean;
> if i just keep refreshing the web request below, i get different documents
> occasionally.
>
> http://satdemo/XML/CustomerWelcome....9B-3F344C282531
>
> I'd like to know if anyone has had this problem, can replicate the
> results,
> provide a solution, or if any MS representatives can pick this up and
> verify
> the problem or provide a solution..
>
> Thank you,
>
> Rainabba ( rainabba@ureach.com )
>
> -----------------------------------------Sample
> Query----------------------------------------------
> <?xsl-serverstylesheet xslfile="CustomerWelcome.xsl" ?>
> <root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <Companyname>
> <sql:query SessionID="">
> select ? SessionId for xml auto
> </sql:query>
> <sql:query>
> exec xmlCustomerWelcome ? for xml auto
> </sql:query>
> </Companyname>
> </root>
> ----------------------------------------------------------------------------
> -------------------------
>
>
>
> ---------------------------------------Sample
> Results------------------------------------------------
> ------ Version 0 (expected and prefered) --------
> <root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <Companyname>
> <StoredProc>
> <SessionId>XXXX-XXXXXXX-XXXXXXXXXX-XXXXXXXXXX</SessionId>
> </StoredProc>
> <Company>
> <SessionId>XXXX-XXXXXXX-XXXXXXXXXX-XXXXXXXXXX</SessionId>
> <Name>0005-06-00 NGB Test Company</Name>
> <FirstName>IAmThe</FirstName>
> <LastName>PERSON</LastName>
> <CompanyID>0</CompanyID>
> <CoPAStatus>0</CoPAStatus>
> <Project>
> <Description>0005-06-00</Description>
> <ProjectID>04</ProjectID>
> <sponsor>0</sponsor>
> <Person>
> <PersonID>0</PersonID>
> <ProjectAs>
> <ProjectRole>0</ProjectRole>
> <PERSONAgree>0</PERSONAgree>
> <BPLIStatus>0</BPLIStatus>
> </ProjectAs>
> </Person>
> </Project>
> </Company>
> </Companyname>
> </root>
>
> ----Version 1 -----
> <root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <Companyname>
> <StoredProc>
> <SessionId>XXXX-XXXXXXX-XXXXXXXXXX-XXXXXXXXXX</SessionId>
> </StoredProc>
> <StoredProc>
> <SessionId>XXXX-XXXXXXX-XXXXXXXXXX-XXXXXXXXXX</SessionId>
> <Name>0005-06-00 NGB Test Company</Name>
> <FirstName>IAmThe</FirstName>
> <LastName>PERSON</LastName>
> <Description>0005-06-00</Description>
> <CompanyID>0</CompanyID>
> <ProjectID>04</ProjectID>
> <PersonID>0</PersonID>
> <sponsor>0</sponsor>
> <ProjectRole>0</ProjectRole>
> <CoPAStatus>0</CoPAStatus>
> <PERSONAgree>0</PERSONAgree>
> <BPLIStatus>0</BPLIStatus>
> </StoredProc>
> </Companyname>
> </root>
> -----------------------
> ----------------------------------------------------------------------------
> -------------------
>
>

Sponsored Links