Hi,
I ran the two queries below on SQL server 2005 enterprise 9.00.1399.06 and 9.00.2047.00. The first ("Top" paginated) query ran fine (from my C# code based dataset) on the older version, but returns results like those below (See Result1) from the newer version. I need to get my data back with the xml parent child nesting intact and table handles as they are designated in the main query text. Perhaps there is another way to do a paginated query that will deliver xml nested as shown in Result2. If so I would like to know how to code it.
If you have any suggestions, I would appreciate any help you can give. Thanks, Dave
Query 1 (Paginated using Top):
select niin, item_name, cage, partno, vendorname, ui, price from (select top 10 niin, item_name, cage, partno, vendorname, ui, price from (select top 30 flis_a.niin,flis_a.item_name, flis_cage.cage,flis_cage.partno,flis_cage.vendorname, pricing.ui,pricing.price from dbFlisCurPlusHist..niindata AS flis_a left outer join dbFlisCurPlusHist..mcrldata AS flis_cage on flis_a.niin = flis_cage.niin left outer join dbFlisCurPlusHist..mlcdata AS pricing on flis_a.niin = pricing.niin where flis_a.niin like '0000%' and flis_a.item_name like '%BOLT%' and flis_cage.cage like '%205%' and pricing.sa ='DN' order by flis_a.niin, flis_cage.niin, pricing.niin) as newtbl order by niin desc) as newtbl2 order by niin asc for xml auto
Query 2:
select flis_a.niin,flis_a.item_name, flis_cage.cage,flis_cage.partno,flis_cage.vendorname, pricing.ui,pricing.price from dbFlisCurPlusHist..niindata AS flis_a left outer join dbFlisCurPlusHist..mcrldata AS flis_cage on flis_a.niin = flis_cage.niin left outer join dbFlisCurPlusHist..mlcdata AS pricing on flis_a.niin = pricing.niin where flis_a.niin like '0000%' and flis_a.item_name like '%BOLT%' and flis_cage.cage like '%205%' and pricing.sa ='DN' order by flis_a.niin, flis_cage.niin, pricing.niin asc for xml auto
Result 1: (Results not nested as they need to be. This ran fine on older version from c# dataset but now fails on the newer sql server)
<newtbl2 niin="000041534" item_name="BOLT,MACHINE" cage="80205" partno="AN7-24A" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000000.68" />
<newtbl2 niin="000041535" item_name="BOLT,SHEAR" cage="80205" partno="NAS1303-12" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000000.23" />
<newtbl2 niin="000045155" item_name="BOLT,MACHINE" cage="80205" partno="AN17-40A" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000009.81" />
<newtbl2 niin="000050435" item_name="BOLT,SHEAR" cage="80205" partno="NAS627H14" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000009.03" />
<newtbl2 niin="000050546" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-5-35" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000012.94" />
<newtbl2 niin="000050549" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-6-19" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000015.52" />
<newtbl2 niin="000050550" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-6-23" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000020.73" />
<newtbl2 niin="000056093" item_name="BOLT,CLOSE TOLERANCE" cage="80205" partno="MS27576-4-27" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000011.12" />
<newtbl2 niin="000061454" item_name="BOLT,INTERNAL WRENCHING" cage="80205" partno="NAS1351-4H12" vendorname="NATIONAL AEROSPACE STANDARDS" ui="EA" price="000000000.25" />
<newtbl2 niin="000062269" item_name="BOLT,SQUARE NECK" cage="80205" partno="MS35751-53" vendorname="NATIONAL AEROSPACE STANDARDS" ui="BX" price="000000005.61" />
-
The above response should look like this with child elements nested, etc and the flis_a table handle intact. The response below ran fine on SQL Server 2005 Enterprise version 9.00.1399.06.
<flis_a niin="000041534" item_name="BOLT,MACHINE"><flis_cage cage="80205" partno="AN7-24A" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000000.68"/></flis_cage></flis_a><flis_a niin="000041535" item_name="BOLT,SHEAR"><flis_cage cage="80205" partno="NAS1303-12" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000000.23"/></flis_cage></flis_a><flis_a niin="000045155" item_name="BOLT,MACHINE"><flis_cage cage="80205" partno="AN17-40A" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000009.81"/></flis_cage></flis_a><flis_a niin="000050435" item_name="BOLT,SHEAR"><flis_cage cage="80205" partno="NAS627H14" vendorname="NATIONAL AEROSPACE STANDARDS"><pricing ui="EA" price="000000009.03"/></flis_cage></flis_a><flis_a niin="000050546" item_name="BOLT,CLOSE TOLERANCE"><flis_cag ...
--
Result 2: (Non paginated and works fine on both versions)
<flis_a niin="000011989" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="NAS1308-29" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000008.65" />
</flis_cage>
</flis_a>
<flis_a niin="000011993" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="NAS464P8-79" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000030.46" />
</flis_cage>
</flis_a>
<flis_a niin="000014780" item_name="BOLT,INTERNAL WRENCHING">
<flis_cage cage="80205" partno="NAS1352-06LE8" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000003.72" />
</flis_cage>
</flis_a>
<flis_a niin="000014807" item_name="BOLT,CLOSE TOLERANCE">
<flis_cage cage="80205" partno="MS27576-5-22" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000013.93" />
</flis_cage>
</flis_a>
<flis_a niin="000014847" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="MS21250-03020" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000001.02" />
</flis_cage>
</flis_a>
<flis_a niin="000014899" item_name="BOLT,MACHINE">
<flis_cage cage="80205" partno="NAS428-3-15" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000000.86" />
</flis_cage>
</flis_a>
<flis_a niin="000016674" item_name="BOLT,SHEAR">
<flis_cage cage="80205" partno="NAS464P5LA33" vendorname="NATIONAL AEROSPACE STANDARDS">
<pricing ui="EA" price="000000002.86" />
</flis_cage>
</flis_a>
.
.
.
Subqueries in the from clause are now treated like views (as they should be) and become opaque for auto mode queries.
You may want to run the queries under compat level 80 (sp_dbcmptlevel 'dbname', 80) if you want the SQL Server 2000 behaviour or rewrite your queries using FOR XML PATH.
Also, it would help if you could provide a schema definition and some sample data to repro the behaviour.
Best regards
Michael
No comments:
Post a Comment