Query returns XML instead of a bunch of rows
I am poking around in a database that was created by a company who is a client of ours. We are writing them a web application. I am just doing some simple functions, like implementing a search for data on some of the web pages (enter a last name, and get a grid populated with all matches to that last name).
So I am just poking around at their tables and sprocs. I have never seen this before. Instead of SELECT blah-blah WHERE some-criteria and getting back 106 rows, they added onto the query:
order by code
FOR XML PATH(''),
ROOT('CityAbbreviations')
And they get one row of XML. Is this done often? Is it because it is more efficient to parse a string of XML than read through a dataset? (I am not sure where the code is that calls such a sproc, to see how it is handled at the front end).
Thanks.
Re: Query returns XML instead of a bunch of rows
we use that syntax a lot here... it can be a convenient way to move data around... it's also a hassle because before we can shove the data into a table, it has to be shredded back into usable data. We use it because we have a lot of heirarchical data... in a very generic sense, let's say we have a Person record... that person can have 0 or more addresses... so when we save this person, we take their addresses, convert it to xml, and pass it in as a parametter to the person save sproc... along with all of the other data (not in xml) like name, etc ... then the sproc updates the person record in the person table... then passes the address xml to the address xml shredder and gets back a temp table... which can then be used to update the address table with the infor for that person.
down side is that this really sucks when you're trying to debug the person save sproc because it means you have to construct and xml document segment that can be passed to the address shredder... blah blah blah...
by the way... you still are getting 106 rows... it's just now all wrapped up in the xml.
whether it's more efficient or not, I don't know... we do it because the platform we develop against is xml based and the front end know what to do with it when it gets it... we just feed it the data... it's pretty slick actually.
-tg
Re: Query returns XML instead of a bunch of rows
If you are writing a web application then having it already XML could make it easy to transmit to the page - although I much prefer JSON for that.
Are you doing a traditional ASP.Net page - or venturing into JQuery/AJAX??
Re: Query returns XML instead of a bunch of rows
Quote:
Originally Posted by
techgnome
by the way... you still are getting 106 rows... it's just now all wrapped up in the xml.
-tg
Well, that is an interesting point. I get back the same data, but isn't it one row and that row is XML with 106 elements?
What is interesting is when I run the query in Management Studio, you know how on the left it will number your rows? There is just a 1. However, at the bottom of the window where it tell you how many rows are returned (and without the FOR XML etc this will say 106 rows), it says 4 rows.
Re: Query returns XML instead of a bunch of rows
Quote:
Originally Posted by
szlamany
If you are writing a web application then having it already XML could make it easy to transmit to the page - although I much prefer JSON for that.
Are you doing a traditional ASP.Net page - or venturing into JQuery/AJAX??
Probably the latter but probably not by me. Our company structure is that there is a design team and a development/engineering team. So the JQuery and AJAX are usually done by the designers. I will be writing the backend code that goes from aspx.cs to the database.
Re: Query returns XML instead of a bunch of rows
Grab this ms download - it's free
http://www.microsoft.com/download/en...s.aspx?id=7973
XML Notepad allows you to look and edit the xml as if it was notepad...
Re: Query returns XML instead of a bunch of rows
yeah, the row count in SSMS is a best guess... it's "4" because if you were to view the XML, it's 4 lines... the results grid is sees it as a single returned entity and so you get "1" ... you should be able to click it, and it opens a new tab with the xml showing... in there you will find your 106 rows. XML is a type in SQL Server... so queries which return XML like that, treat it as a type, making it a single entity and thus, the one row in the results.
-tg
Re: Query returns XML instead of a bunch of rows
Quote:
Originally Posted by
techgnome
yeah, the row count in SSMS is a best guess... it's "4" because if you were to view the XML, it's 4 lines... the results grid is sees it as a single returned entity and so you get "1" ... you should be able to click it, and it opens a new tab with the xml showing... in there you will find your 106 rows. XML is a type in SQL Server... so queries which return XML like that, treat it as a type, making it a single entity and thus, the one row in the results.
-tg
I click and get this:
<CityAbbreviations>
<code>AG</code> = <description>Allegany</description><a></a>
and then the next code and description, etc. But I still wouldn't call them "rows".
Re: Query returns XML instead of a bunch of rows
Quote:
Originally Posted by
szlamany
Yes, I will try that out. Thanks.
Re: Query returns XML instead of a bunch of rows
If you have to serve out the data (appears you are writing the data access layer) in something other than XML - like a dataset for some kind of asp.net grid - than having it come out of SQL as XML seems a problem.
If you are going to serve it out to AJAX calls to a web service than you are going to want to do it in JSON - if the front-end folks appreciate the JSON-fit-into-Jquery/Javascript fit...
