FOR XML returning chunked data?
-
Hi all,
Have been lurking for a while now, I've learnt a lot from these forums but I'm at the stage where I need a little hands on help, if possible.
I have a query that returns a fairly simple xml packet,
<item>
<id>1234</id>
<name>test</name>
<active>1</active>
</item>
It's not representative of my actual xml but that's beside the point. Lets say that SQL returns 3000 nodes based on the packet above. SQL seems to chunk this data into separate rows, which can be seen in the bottom right of a query window. It has no relation to the number of records it's found, just the number of rows that the xml has been split into.
My question is this, can this XML string be streamed in one go rather than returned in chunks? In my server side code I have to loop over the query and concat every row in order to see the full XML packet.
Hope this makes sense 🙂
Cheers!
Steve
-
Chandler3224 (8/25/2011)
Hi all,Have been lurking for a while now, I've learnt a lot from these forums but I'm at the stage where I need a little hands on help, if possible.
I have a query that returns a fairly simple xml packet,
<item>
<id>1234</id>
<name>test</name>
<active>1</active>
</item>
It's not representative of my actual xml but that's beside the point. Lets say that SQL returns 3000 nodes based on the packet above. SQL seems to chunk this data into separate rows, which can be seen in the bottom right of a query window. It has no relation to the number of records it's found, just the number of rows that the xml has been split into.
My question is this, can this XML string be streamed in one go rather than returned in chunks? In my server side code I have to loop over the query and concat every row in order to see the full XML packet.
Hope this makes sense 🙂
Cheers!
Steve
I would like to help, but I have more questions than direction on what you need. Can you please provide sample tables, data, a query that demonstrates the results you have an issue with and an example of how you want the results to be returned?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I agree with Orlando.
Too vague to provide a sound answer at this point.
The only thing I can think of is the output format in SSMS that's causing some confusion.
Where do you want to "see" the result? Will it get further processed internally, send to an application or stored in a file?
Do you store the result in a variable? If so, what data type?
-
Hey guys. Thanks for replying. Fair point about the details. I'm away from the computer at the moment but I shall upload an example as soon as. Please bear with me, cheers!
-
Okay, here's the CREATE
CREATE TABLE [dbo].[tbltest](
[id] [int] NULL,
[name] [varchar](50) NULL,
[value] [varchar](50) NULL
) ON [PRIMARY]
GO
Here's some data
DECLARE @count INT
SET @count = 0
WHILE (@count < 100)
BEGIN
INSERT INTO tbltest (id, name, value) VALUES (@count, 'val1', 'val2')
SET @count = (@count + 1)
END
And here's a sample query
SELECTid as "id",
name as "name",
value as "value"
FROMtbltest
FOR XML PATH('item'), ROOT('items')
If you run the sql in a query window you'll see that it returns an XML packet but the bottom right shows "5 rows". I'm using ColdFusion and, upon running this query, I have 5 rows returned to me. I have to loop through the result and concat the row data in order to have a final xml packet.
My question is if it is possible to return the entire packet which isn't split over X rows.
See the image below for an example of my "5 rows" view.
I'm using MSSQL 2008. Cheers guys, thanks very much for the offer of help.
-
FOR XML is only shaping the result before it is returned to you, but after the DB Engine runs the query and gets the rows of data just as any other query would be executed.
The number shown in SSMS is a lie in terms of the XML shaped result so just ignore it.
My guess is that you're having an issue with the JDBC driver ColdFusion is using. Try your query like this to guarantee you receive a scalar in return:
IF EXISTS ( SELECT *FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[tbltest]')
AND type IN (N'U') )
DROP TABLE [dbo].[tbltest] ;
GO
CREATE TABLE [dbo].[tbltest]
(
[id] [int] NULL,
[name] [varchar](50) NULL,
[value] [varchar](50) NULL
)
ON
[PRIMARY]
GO
DECLARE @count INT
SET @count = 0
WHILE (@count < 100)
BEGIN
INSERT INTO tbltest
(id, name, value)
VALUES (@count, 'val1', 'val2')
SET @count = (@count + 1)
END
GO
DECLARE @xml XML
SET @xml = (
SELECT id AS "id",
name AS "name",
value AS "value"
FROM tbltest
FOR
XML PATH('item'),
ROOT('items')
) ;
SELECT @xml as xml_data ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Brilliant! This works, exactly the result I wanted.
Are there any cons to returning the data this way? I can't see any, really. I'll be returning 3000 nodes which have 30 elements each, it's a lot of data.
Once again, thanks for the quick response 😀
-
Cool, HTH. The XML type has a theoretical limit of 2GB in size (~1MM characters in length, it's stored using UTF-16 internally) but IIRC some of the 2GB is used for overhead of the XML type itself. Driver issues asdie, no issues I can think of other than the usual...only get the data you need because there is no free lunch 🙂 Large data from I/O system and through memory of DB server, across network, into web server memory, etc. etc. </soapbox>
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I think it would probably be a simpler solution to just add a TYPE directive to the original query (which returns the result as an explicity XML type):
SELECT id as "id",name as "name",
value as "value"
FROM tbltest
FOR XML PATH('item'), ROOT('items'), type
Although I'm not seeing the "5 rows returned" that you are - I get 1 row for both versions of the query. Strange...
-
I'm not an expert but shouldn't the query have "active" somewhere in it in order to return
<active>1</active>
?
Giorgio
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply