Forcing matrix static elements to display despite empty record set

I created a nice matrix report and it works great when the parameter I pass
returns some records, however when no records are returned the matrix simply
does not render at all leaving a big empty hole where that part of the report
should appear.
The matrix has static row heading that I want to always appear even if no
data appears in the columns, even better if there was a way to set a default
value to the columns in case no data is returned say 0s. This is an example
of my matrix.
Static Heading
Column Group
Cars
people
animals
So, Cars, People, and Animals are static row headings and the values fill in
next to them for the column groupings. Now, if no records are returned I
would still like to have the static column heading and static row heading to
appear even if there is no data to display. How is that done?
ThanksTry setting something in the NoRows property - like display a message "no
data for this timeframe" or something and I think that will make your
headings show in addition to the message. I have used this for tables ...
havent tried it with a matrix but I would think it should work.
"Ramez" wrote:
> I created a nice matrix report and it works great when the parameter I pass
> returns some records, however when no records are returned the matrix simply
> does not render at all leaving a big empty hole where that part of the report
> should appear.
> The matrix has static row heading that I want to always appear even if no
> data appears in the columns, even better if there was a way to set a default
> value to the columns in case no data is returned say 0s. This is an example
> of my matrix.
> Static Heading
> Column Group
> Cars
> people
> animals
>
> So, Cars, People, and Animals are static row headings and the values fill in
> next to them for the column groupings. Now, if no records are returned I
> would still like to have the static column heading and static row heading to
> appear even if there is no data to display. How is that done?
> Thanks
>
>
>

Force Uniqueness on one column

HI:
If when joining parent and child tables, a query returns multiple entries
for a given parent, how can I limit query to showing only first child? Kind
of like grouping on one field in result set.
Thanks,
CharlieDefine "first".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
> HI:
> If when joining parent and child tables, a query returns multiple entries
> for a given parent, how can I limit query to showing only first child?
> Kind
> of like grouping on one field in result set.
> Thanks,
> Charlie
>|||Hi Tom, let me restate..
If query joins a parent table with a child table in a one-to-many relation
the results set will show the parent id repeating for each child. I want
the query to show only one child despite having many. How do I filter join
to limit result set to only one child per parent even though it a parent may
have many child records.
Thanks,
charlie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> Define "first".
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
entries
>|||SELECT
Parent.ID
, Child.ID
, Child.Data
FROM
Parent
INNER JOIN
(
SELECT
Child.Parent_ID
, Child.ID
, Child.Data
FROM
Child
INNER JOIN
(
SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
Parent_ID
) LowestChildForParent
ON
Child.Parent_ID = LowestChildForParent.Parent_ID
AND
Child.ID = LowestChildForParent.ID
) Child
ON
Parent.ID = Child.Parent_ID
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
join
> to limit result set to only one child per parent even though it a parent
may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Again, define "first". You haven't posted your DDL. We have no idea which
of the child rows is the "first" for a given parent ID.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
> join
> to limit result set to only one child per parent even though it a parent
> may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Using min() or max() value for a set of keys in grouping should work. This
will first or last child.
Thanks
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:437a10a3$0$133$7b0f0fd3@.mistral.news.newnet.co.uk...
> SELECT
> Parent.ID
> , Child.ID
> , Child.Data
> FROM
> Parent
> INNER JOIN
> (
> SELECT
> Child.Parent_ID
> , Child.ID
> , Child.Data
> FROM
> Child
> INNER JOIN
> (
> SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
> Parent_ID
> ) LowestChildForParent
> ON
> Child.Parent_ID = LowestChildForParent.Parent_ID
> AND
> Child.ID = LowestChildForParent.ID
> ) Child
> ON
> Parent.ID = Child.Parent_ID
>
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
relation
want
> join
> may
child?
>

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')

FOR XML output - strange behavior

We have an application that executes a SQL statement
SELECT * FROM tablename FOR XML AUTO, ELEMENTS and returns the data as XML.
However, we are running into the issue where after 2048 characters a carriag
e
is inserted in the output and thus the XML ends up not being valid.
Is it a known issue? Is there any workaround?
We have SQL Server 2000 Entp edition with SP4.
Thanks for any input.
J JustinHello J,
As far as I recall, no, there's no particular issue with this if you're usin
g
a tool that reads it all as byte stream. Are you sure the data in question
doesn't have a return? How are you reading the data?
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks Kent. I checked my data again. You are right. Carriage return is also
stored on couple of rows. If I exclude those rows in the WHERE clause, then
all are working fine. A custom developed web service is using this data.
How to make sure that SQL query with FOR XML statement will return all data
without issues regardless of whether carriage return is present in a row or
not?
J Justin
"Kent Tegels" wrote:

> Hello J,
> As far as I recall, no, there's no particular issue with this if you're us
ing
> a tool that reads it all as byte stream. Are you sure the data in question
> doesn't have a return? How are you reading the data?
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>|||Hello J,
a.) clean up the existing data to remove the returns
b.) enforce good data validation on entry to not allow returns
c.) consider using an FOR XML EXPLICT query to emit the questionable field
as CDATA (I think this is possible)
kt

FOR XML output - strange behavior

We have an application that executes a SQL statement
SELECT * FROM tablename FOR XML AUTO, ELEMENTS and returns the data as XML.
However, we are running into the issue where after 2048 characters a carriage
is inserted in the output and thus the XML ends up not being valid.
Is it a known issue? Is there any workaround?
We have SQL Server 2000 Entp edition with SP4.
Thanks for any input.
J Justin
Hello J,
As far as I recall, no, there's no particular issue with this if you're using
a tool that reads it all as byte stream. Are you sure the data in question
doesn't have a return? How are you reading the data?
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

FOR XML EXPLICIT - Gaps in result set

I have a query that returns four levels of nested data. The problem is that in the data is missing in sporadic places in the result set. Is there a size limitation in the result set?
SQL Server is returning what looks to be multiple result sets to the client. The "gaps" in data seem to coincide with the end of one result set and the start of the next result set.
Is there an option I can set to return one result set?
Any help with this would be greatly appreciated.
Here is my query:
/************************************************** ************
Return the results as an XML string.
************************************************** *************/
Exec ('Select 1 as Tag,
Null as Parent,
t.[Name] As [GrandParent!1!Name!xml],
Null As [Parents!2!Name!xml],
Null As [Children!3!Name!xml],
Null As [Children!3!Description!xml],
Null As [GrandChildren!4!Name!xml],
Null As [GrandChildren!4!Description!xml]
From GrandParent t
Union All
Select 2 As Tag,
1 As Parent,
t.[Name],
p.[Name] As [Parents!2!Name!xml],
Null As [Children!3!Name!xml],
Null As [Children!3!Description!xml],
Null As [GrandChildren!4!Name!xml],
Null As [GrandChildren!4!Description!xml]
From GrandParent t, Parents p
Where t.MyID = p.ParentID
Union All
Select 3 As Tag,
2 As Parent,
t.[Name],
p.[Name] As [Parents!2!Name!xml],
c.[Name] As [Children!3!Name!xml],
c.[Description] As [Children!3!Description!xml],
Null As [GrandChildren!4!Name!xml],
Null As [GrandChildren!4!Description!xml]
From GrandParent t, Parents p, Children c
Union All
Select 4 As Tag,
3 As Parent,
t.[Name],
p.[Name] As [Parents!2!Name!xml],
c.[Name] As [Children!3!Name!xml],
c.[Description] As [Children!3!Description!xml],
g.[Name] As [GrandChildren!4!Name!xml],
g.[Description] As [GrandChildren!4!Description!xml]
From GrandParent t, Parents p, Children c, GrandChildren g
Order By [GrandParent!1!Name!xml], [Parents!2!Name!xml], [Children!3!Name!xml], [GrandChildren!4!Name!xml]
For XML Explicit')
A sample of the results follows (Unfortunately my formating did not come thru in the post so I manually indented. My comments are preceeded by ***):
<GrandParent><Name>Investment Practice</Name><Parents><Name>AAA - Asset Allocation Analysis/Strategy</Name><Children><Name>Industries Followed</Name><Description>Industries Followed</Description><GrandChildren><Name>CNS1 - Asset Allocation Strategy</Name>
***This is a different GrandChild Node The data between The start of the previous Grandchildren node(CNS1) and the "
n-cyclical Consumer Goods</Name> is gone. ***
<
n-cyclical Consumer Goods</Name><Description>EAI5</Description></GrandChildren><GrandChildren><Name>EAI6 - Health Care / Non-cyclical Services</Name><Description>EAI6</Description></GrandChildren><GrandChildren><Name>EAI7 - Financials</Name><Description>E
A
***This is a different GrandChild Node The data between The start of the previous Grandchildren node(EAI7) and the "me> is gone. ***
me>FIS1 - Treasuries/Sovereign/Agencies/TIPS</Name><Description>FIS1</Description></GrandChildren>
***This is what a GrandChild node should look like.
<GrandChildren><Name>FIS2 - Corporate - Investment Grader</Name><Description>FIS2</Description></GrandChildren><GrandChildren><Name>FIS3 - Mortgage Backed/XXX Portfolio</Name><Description>RMS4</Description></GrandChildren>
Further investigation reveals that the result is being broken in to 256 character result sets with gaps in the data between result sets.
Is there a conguration setting to increase the size? I haven't been able to find anything yet.
Can anyone help?
|||It finally dawned on me to check the options in Query Analyzer and I was able to change the default column width to the maximum of 8192.
This looks better. I am still getting multiple result sets, but I don't see any gaps (yet).
|||QA uses ODBC which is not supporting the FOR XML stream output well. You
should use ADO, OLEDB or ADO.net in order to programmatically retrieve FOR
XML results from the database.
Best regards
Michael
"Casey Loranger" <anonymous@.discussions.microsoft.com> wrote in message
news:FC5B9E3D-1177-4576-837E-C2C761FF0919@.microsoft.com...
> It finally dawned on me to check the options in Query Analyzer and I was
> able to change the default column width to the maximum of 8192.
> This looks better. I am still getting multiple result sets, but I don't
> see any gaps (yet).
>

FOR XML AUTO, ELEMENTS

SELECT ... FOR XML AUTO, ELEMENTS returns a blob
My buisinessappl. can't retrieve a blob from a storedprocedure
Is there anyway i can convert the result in the storedprocedure to a text or
varchar
before returning it to my Buisinessappl.
Or maybe there is a property in MSSQL SERVER that i can change to fix this
Jens
Are you using SQL Server 2000 or 2005?
Can you change the client code to get the stream back if you are using SQL
Server 2000?
Best regards
Michael
"Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
> SELECT ... FOR XML AUTO, ELEMENTS returns a blob
> My buisinessappl. can't retrieve a blob from a storedprocedure
> Is there anyway i can convert the result in the storedprocedure to a text
> or
> varchar
> before returning it to my Buisinessappl.
> Or maybe there is a property in MSSQL SERVER that i can change to fix this
> Jens
|||I'm using SQL Server 2000 and on the clientside a appl built with
PowerBuilder 10.
Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
retrieves one row from the StoredProcedure but the one column that should
contain the XML is empty.
I need to somehow convert the result, varchar(32766) will do fine.
Is it possible to save the result from a SELECT .. FOR XML AUTO statement in
the database
regards
Jens
"Michael Rys [MSFT]" skrev:

> Are you using SQL Server 2000 or 2005?
> Can you change the client code to get the stream back if you are using SQL
> Server 2000?
> Best regards
> Michael
> "Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
> news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
>
>
|||If you use SQL Server 2000, you have to use the ADO/OLEDB ICommandStream
interface to get the FOR XML result back as a stream and not a rowset.
And there is no easy, performant way to assign the result of a FOR XML query
to a variable or column in SQL Server 2000. You would have to upgrade to SQL
Server 2005 to get this functionality.
Best regards
Michael
"Jens Mardh" <JensMardh@.discussions.microsoft.com> wrote in message
news:E54AEC3C-3783-4930-8F34-4C782FC72471@.microsoft.com...[vbcol=seagreen]
> I'm using SQL Server 2000 and on the clientside a appl built with
> PowerBuilder 10.
> Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
> retrieves one row from the StoredProcedure but the one column that should
> contain the XML is empty.
> I need to somehow convert the result, varchar(32766) will do fine.
> Is it possible to save the result from a SELECT .. FOR XML AUTO statement
> in
> the database
> regards
> Jens
> "Michael Rys [MSFT]" skrev:

FOR XML AUTO, ELEMENTS

SELECT ... FOR XML AUTO, ELEMENTS returns a blob
My buisinessappl. can't retrieve a blob from a storedprocedure
Is there anyway i can convert the result in the storedprocedure to a text or
varchar
before returning it to my Buisinessappl.
Or maybe there is a property in MSSQL SERVER that i can change to fix this
JensAre you using SQL Server 2000 or 2005?
Can you change the client code to get the stream back if you are using SQL
Server 2000?
Best regards
Michael
"Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
> SELECT ... FOR XML AUTO, ELEMENTS returns a blob
> My buisinessappl. can't retrieve a blob from a storedprocedure
> Is there anyway i can convert the result in the storedprocedure to a text
> or
> varchar
> before returning it to my Buisinessappl.
> Or maybe there is a property in MSSQL SERVER that i can change to fix this
> Jens|||I'm using SQL Server 2000 and on the clientside a appl built with
PowerBuilder 10.
Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
retrieves one row from the StoredProcedure but the one column that should
contain the XML is empty.
I need to somehow convert the result, varchar(32766) will do fine.
Is it possible to save the result from a SELECT .. FOR XML AUTO statement in
the database
regards
Jens
"Michael Rys [MSFT]" skrev:

> Are you using SQL Server 2000 or 2005?
> Can you change the client code to get the stream back if you are using SQL
> Server 2000?
> Best regards
> Michael
> "Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
> news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
>
>|||If you use SQL Server 2000, you have to use the ADO/OLEDB ICommandStream
interface to get the FOR XML result back as a stream and not a rowset.
And there is no easy, performant way to assign the result of a FOR XML query
to a variable or column in SQL Server 2000. You would have to upgrade to SQL
Server 2005 to get this functionality.
Best regards
Michael
"Jens Mardh" <JensMardh@.discussions.microsoft.com> wrote in message
news:E54AEC3C-3783-4930-8F34-4C782FC72471@.microsoft.com...
> I'm using SQL Server 2000 and on the clientside a appl built with
> PowerBuilder 10.
> Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
> retrieves one row from the StoredProcedure but the one column that should
> contain the XML is empty.
> I need to somehow convert the result, varchar(32766) will do fine.
> Is it possible to save the result from a SELECT .. FOR XML AUTO statement
> in
> the database
> regards
> Jens
> "Michael Rys [MSFT]" skrev:
>

FOR XML AUTO returns too many additional elements

Hi, I use the FOR XML AUTO to retrive native XML from a database with:

SELECT [xml] FROM myxml WHERE id = 81 FOR XML AUTO, elements, root('ROOT')"

However it returns the database name and table name as parent elements. How can I return just my raw XML data without additional elements:

XML is Stored:

<ROOT>

<CHAPTER>

<TITLE>This is a test</TITLE>

</CHAPTER>

</ROOT>

Returns:

<databasename>

<tablename>

<ROOT>

<CHAPTER>

<TITLE>This is a test</TITLE>

</CHAPTER>

</ROOT>

</tablename>

</databasename>

I got it. I used XQuery to get the xml...

FOR XML AUTO returns incomplete xml

I have two SQL tables that are populated based on data in SQL system tables.
When I run the FOR XML AUTO select statement on these tables, certain fields
will be missing end tags AND the data will not all be returned. In some
cases the end tags are there but the data is incomplete. When running the
query against the actual system table I'll get similar results but not
exactly the same. Any suggestions?
Using sql 2000 sp3. SQLXML 3 sp2
--master..sysaltfiles table data. Return only a few db's and an incomplete
select rtrim(filename) filename from mridiag..tbldbfiles for xml auto
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Data.MDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Log.LDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\master.mdf
NT\data\pubs_log.ldf
(24 row(s) affected)
--master..sysprocesses table. Only returns 1 incomplete record
select LASTWAITTYPE test from master..sysprocesses for xml auto
<master..sysprocesses test="SLEEP
(15 row(s) affected)
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
>I have two SQL tables that are populated based on data in SQL system
>tables.
> When I run the FOR XML AUTO select statement on these tables, certain
> fields
> will be missing end tags AND the data will not all be returned. In some
> cases the end tags are there but the data is incomplete. When running the
> query against the actual system table I'll get similar results but not
> exactly the same. Any suggestions?
What client are you using to retrieve the results?
You might also check this FAQ:
http://sqlxml.org/faqs.aspx?faq=76
Bryant
|||Thanks for the response.
I reviewed the link to the FAQ and compared it to how I'm doing it. First,
the information I posted was using Query Analyzer but I get the same results
when executing it from my vb script.
I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
link I based my code on is below. It's basically the same as the "VB
Example" on the faq you pointed me to but the version of XML on the FAQ is
3.0 and the version used in my script is 4.0. Other than that, I can't see
any differences.
Any more suggestions or questions? It really has me puzzled.
Thanks again.
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||Here is the link I mentioned.
http://www.sqlxml.org/faqs.aspx?faq=10
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||The query analyzer is using ODBC and not the OLEDB stream object and thus
only get junked XML back. Also, unless you increase the number of bytes
displayed per line, it does drop information.
If you are using the SQLOLEDB stream interface, you should get the XML back.
Can you try it with the SQLXML HTTP component to see if the XML is correctly
generated by the FOR XML query?
Thanks
Michael
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:9679959F-BC15-48CC-B4F9-7B521D883DCC@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> I reviewed the link to the FAQ and compared it to how I'm doing it.
> First,
> the information I posted was using Query Analyzer but I get the same
> results
> when executing it from my vb script.
> I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
> link I based my code on is below. It's basically the same as the "VB
> Example" on the faq you pointed me to but the version of XML on the FAQ is
> 3.0 and the version used in my script is 4.0. Other than that, I can't
> see
> any differences.
> Any more suggestions or questions? It really has me puzzled.
> Thanks again.
> "Bryant Likes" wrote:

FOR XML AUTO returning blank.

Hello,
I have an SQL statement which returns 2 rows
If I append the "FOR XML AUTO" at the end of the query I get nothing back.
The SQL is
Your post was incomplete. Could you post again? and possibly paste the
output of the SELECT query (without the FOR XML AUTO)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"msnews.microsoft.com" <wachaca.no@.spam.santiago.cl> wrote in message
news:uLLueH4bEHA.2816@.TK2MSFTNGP11.phx.gbl...
Hello,
I have an SQL statement which returns 2 rows
If I append the "FOR XML AUTO" at the end of the query I get nothing back.
The SQL is
|||Hi,
the NULL value are not returned with FOR XML
I hope to help you
@.+
Boss Hog
"msnews.microsoft.com" <wachaca.no@.spam.santiago.cl> a crit dans le message
de news:uLLueH4bEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have an SQL statement which returns 2 rows
> If I append the "FOR XML AUTO" at the end of the query I get nothing back.
> The SQL is
>

FOR XML - Root Element

Hi,
Does anybody know how to add a root element to the output of a stored
procedure that returns data, using FOR XML statement. I am using SQL server
2000.
Thanks
Your provider should have the ability to set the root property. For example,
the ADO ICommandStream has a property that will add a root element. Ditto
for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks
|||Michael,
Thanks for your response.
I can't find any property that will allow me to specify a root element. I
am using a XmlReader and sqlCommand to serialize an object based on the
results of a stored procedure which returns XML.
Due to our environment it is not possible to use a SqlXMLcommand object.
Thanks for any further help.
Alex
"Michael Rys [MSFT]" wrote:

> Your provider should have the ability to set the root property. For example,
> the ADO ICommandStream has a property that will add a root element. Ditto
> for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
> Best regards
> Michael
> "Redowl" <Redowl@.discussions.microsoft.com> wrote in message
> news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
>
>
|||I think you have to use the SQLXML interfaces to add the root property.
In SQL Server 2000, you can also add a select N'<root>' before and select
N'</root>' after the stored proc invocation, IF you use the stream
interface.
Note that this is however not guaranteed to work well with the XML datatype
in 2005.
Also, in SQL Server 2005, you will be able to specify the root in your FOR
XML clause using a new ROOT directive...
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:0DACE436-377C-489B-A378-166ADEB6F0D5@.microsoft.com...[vbcol=seagreen]
> Michael,
> Thanks for your response.
> I can't find any property that will allow me to specify a root element. I
> am using a XmlReader and sqlCommand to serialize an object based on the
> results of a stored procedure which returns XML.
> Due to our environment it is not possible to use a SqlXMLcommand object.
> Thanks for any further help.
>
> Alex
> "Michael Rys [MSFT]" wrote:
|||In case you are still contemplating the answer to this in SQL Server 2000,
here is a great white paper from Dan Sullivan on how to achieve this:
http://www.sqlservicebroker.com/samp...medxmlauto.zip
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks

FOR XML - Root Element

Hi,
Does anybody know how to add a root element to the output of a stored
procedure that returns data, using FOR XML statement. I am using SQL server
2000.
ThanksYour provider should have the ability to set the root property. For example,
the ADO ICommandStream has a property that will add a root element. Ditto
for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks|||Michael,
Thanks for your response.
I can't find any property that will allow me to specify a root element. I
am using a XmlReader and sqlCommand to serialize an object based on the
results of a stored procedure which returns XML.
Due to our environment it is not possible to use a SqlXMLcommand object.
Thanks for any further help.
Alex
"Michael Rys [MSFT]" wrote:

> Your provider should have the ability to set the root property. For exampl
e,
> the ADO ICommandStream has a property that will add a root element. Ditto
> for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
> Best regards
> Michael
> "Redowl" <Redowl@.discussions.microsoft.com> wrote in message
> news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
>
>|||I think you have to use the SQLXML interfaces to add the root property.
In SQL Server 2000, you can also add a select N'<root>' before and select
N'</root>' after the stored proc invocation, IF you use the stream
interface.
Note that this is however not guaranteed to work well with the XML datatype
in 2005.
Also, in SQL Server 2005, you will be able to specify the root in your FOR
XML clause using a new ROOT directive...
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:0DACE436-377C-489B-A378-166ADEB6F0D5@.microsoft.com...
> Michael,
> Thanks for your response.
> I can't find any property that will allow me to specify a root element. I
> am using a XmlReader and sqlCommand to serialize an object based on the
> results of a stored procedure which returns XML.
> Due to our environment it is not possible to use a SqlXMLcommand object.
> Thanks for any further help.
>
> Alex
> "Michael Rys [MSFT]" wrote:
>|||In case you are still contemplating the answer to this in SQL Server 2000,
here is a great white paper from Dan Sullivan on how to achieve this:
http://www.sqlservicebroker.com/sam...rmedxmlauto.zip
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks

For Update of Cursor in a UDF

Hi,
I am writing a UDF that returns a table variable. In the UDF, I have a
cursor that I want to update. I am getting a syntax error on the UPDATE.
Is there a reason I cannot do this is a user defined function?
Thanks
SteveYou cannot update data inside of a UDF. That restriction is in place,
AFAIK, to avoid some logic problems that might occur, e.g., if a scalar UDF
is being called row-by-row and updates rows that have already been
processed.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:3FE89A39-AB3F-4464-BBB3-ED652EB80D0A@.microsoft.com...
> Hi,
> I am writing a UDF that returns a table variable. In the UDF, I have a
> cursor that I want to update. I am getting a syntax error on the UPDATE.
> Is there a reason I cannot do this is a user defined function?
> Thanks
> Steve|||>> I am writing a UDF that returns a table variable. In the UDF, I have a
You cannot do any updates which changes the persisted data or the database
state from within a UDF. This is by design and documented in SQL Server
Books Online.
Perhaps if you post your overall requirements with relevant information,
others might suggest an alternative. Using a cursor inside a table-valued
UDF for updating certain data seems a very convoluted route.
Anith|||Why are you calling a UDF from a cursor? And are you sure you need to
use a cursor at all?
Please post DDL, sample data and explain your required end result if
you need more help.
David Portas
SQL Server MVP
--|||Thanks for all the responses, obviously, my approach was not too popular.
I like baseing ACCESS reports off of UDF table variables. For this
particular report, I need to process a lot of data and it required me to use
a cursor, and I wanted to update a column so the next pass through would kno
w
I had been there. If my UDF, I gather all this information and Insert it
into the table variable, then that is returned to ACCESS.
It is nice doing it with a UDF cause of the table variable. If I use a
Stored Proc, I would have to CREATE a temp table in the Proc and populate it
,
then base the report on the temp table, if it is still around.
Steve
"Anith Sen" wrote:

> You cannot do any updates which changes the persisted data or the database
> state from within a UDF. This is by design and documented in SQL Server
> Books Online.
> Perhaps if you post your overall requirements with relevant information,
> others might suggest an alternative. Using a cursor inside a table-valued
> UDF for updating certain data seems a very convoluted route.
> --
> Anith
>
>|||I think he's calling a cursor from a UDF :)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124296660.434609.202840@.g14g2000cwa.googlegroups.com...
> Why are you calling a UDF from a cursor? And are you sure you need to
> use a cursor at all?
> Please post DDL, sample data and explain your required end result if
> you need more help.
> --
> David Portas
> SQL Server MVP
> --
>|||>> For this particular report, I need to process a lot of data and it
Cursors are seldom required for data updates. In most cases, you'd write a
single UPDATE statement, preferably within a stored procedure to do any
updates, but it depends on what you exactly meant by "process"
If you are interested in getting some additional assistance, please go
through www.aspfaq.com/5006 and post relevant information for others to
better understand your problem scenario.
Anith|||Thanks for the information. I don't update via a Cursor that often, but in
this case, it was sitting on the row I wanted update, and just thought it
would be convienient. Either way though, if I can't do any updates in a UDF
,
I am taking the wrong approach.
Thanks
"Anith Sen" wrote:

> Cursors are seldom required for data updates. In most cases, you'd write a
> single UPDATE statement, preferably within a stored procedure to do any
> updates, but it depends on what you exactly meant by "process"
> If you are interested in getting some additional assistance, please go
> through www.aspfaq.com/5006 and post relevant information for others to
> better understand your problem scenario.
> --
> Anith
>
>