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 order of XML elements in FOR XML EXPLICIT

Hello,

I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.

I want to generate XML like the following:

<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>

When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?

Here is an example query (does not work, but illustrates what I would like):

select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1

union all

select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1

order by [employee!1!id!element], tag

for xml explicit

I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.

Any ideas?

Thanks!

Trev

I have the same problem. Is there a solution?

Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."

Could someone post an example of how to do it this way please?

Force order of XML elements in FOR XML EXPLICIT

Hello,

I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.

I want to generate XML like the following:

<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>

When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?

Here is an example query (does not work, but illustrates what I would like):

select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1

union all

select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1

order by [employee!1!id!element], tag

for xml explicit

I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.

Any ideas?

Thanks!

Trev

I have the same problem. Is there a solution?

Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."

Could someone post an example of how to do it this way please?

For XML: create elements using cell values

Hi All,
If have a sql table with 2 columns and 2 rows with values
[["col1row1","col2row1"],["col1row2","col2row2"]].

Using t-SQL with "for xml"
How can i create a xml where the cell values (not column names) appear
as elements?
eg:
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2
Thanks,

slyi-- It can be done, but remember that you will have to
-- escape all the XML yourself

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

select 1 as Tag,
null as Parent,
'<'+col1+'>'+col2+'</'+col1+'>' as [TestNode!1!!xml]
from #test

order by Tag,[TestNode!1!!xml]
for xml explicit

drop table #test|||Thanks thats exactly what i needed to know|||On closer examination this wont work it gives

<TestNode><col1row1>col2row1</col1row1></TestNode>
<TestNode><col1row2>col2row2</col1row2></TestNode>
while i need something like
<TestNode>
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2>
</TestNode|||Unless someone else knows better, you're out of luck. Perhaps
you could look at redesigning the XML you are generating
and then apply an XSL transformation at the client.|||Thanks Mark. Could i create a temp table, with the cell values as
columns and build a sql xml query or loop from there?
Although im not too sure if that would work, very efficiently?|||(adrianca@.gmail.com) writes:
> Thanks Mark. Could i create a temp table, with the cell values as
> columns and build a sql xml query or loop from there?
> Although im not too sure if that would work, very efficiently?

I can't see that you can do this in SQL 2000 at all. Well, you can
build an nvarchar string that has the XML, and forego FOR XML
altogether, but if you exceed 4000 characters you lose anyway.

I think you need to build this document client-side.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--something like below is what i was thinking but is it efficent?
--as the client side asp code works out very slow thats why i want to
do it on the sql server if possible

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

create table #xmltree( xmlblob text)
INSERT INTO #xmltree VALUES ('<table>')

Declare @.sqlq varchar(4000)
DECLARE @.textptr varbinary(16)
DECLARE @.bigtext varchar(8000)
DECLARE @.textlen int
DECLARE @.col1 varchar(32), @.col2 varchar(32)

SELECT @.textptr=TEXTPTR(xmlblob) FROM #xmltree

DECLARE tst_cursor CURSOR FOR select * from #test
OPEN tst_cursor
FETCH NEXT FROM tst_cursor into @.col1, @.col2
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.bigtext='<'+ @.col1 + '>' +@.col2+'</'+ @.col1 + '>'
set @.textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @.textptr @.textlen 0 @.bigtext
FETCH NEXT FROM tst_cursor into @.col1, @.col2
END
CLOSE tst_cursor
DEALLOCATE tst_cursor
SET @.bigtext='</table>'
set @.textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @.textptr @.textlen 0 @.bigtext

select xmlblob from #xmltree

drop table #test
drop table #xmltree|||(adrianca@.gmail.com) writes:
> --something like below is what i was thinking but is it efficent?

More to the point: does it work?

> create table #xmltree( xmlblob text)
> INSERT INTO #xmltree VALUES ('<table>')

There is not really any way go get the xml from FOR XML into the table.
Well, you can get it to the client, and then INSERT back. Please don't
that. You're wasting bandwidth.

> --as the client side asp code works out very slow thats why i want to
> do it on the sql server if possible

For this sort of task, I would expect VBscript to be faster than T-SQL,
since we are only doing string manipulation.

You could write a program in C or C# for the task, but then you would have
to pass the XML string to the C program in some way. If you go by file,
you probably lose on the swings what you gain on the roundabout.

I should add the disclaimer that I have no knowledge about ASP
programming.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||asp 6 and asp.net both took about 10 sec to create the xml client side
from a sql table for a 50k table to xml

Using this method it now takes about 2 sec's by just displaying
resultset.

<%@. Page Language="C#" %
<%@. Import Namespace="System.Data.SqlClient" %
<script runat="server">
SqlConnection sqlConnection1;
SqlCommand sqlCommand1;

void Page_Load(Object Sender, EventArgs e) {

sqlConnection1 = new System.Data.SqlClient.SqlConnection();
sqlCommand1 = new System.Data.SqlClient.SqlCommand();
sqlConnection1.ConnectionString = "some connection details";
sqlConnection1.Open();
sqlCommand1.Connection = this.sqlConnection1;
sqlCommand1.CommandText = "sp_getaxml_dataisland";
Response.ContentType = "text/xml";
Response.Write(sqlCommand1.ExecuteScalar().ToStrin g());

}
</script
For me thats a performance gain worth taking.|||(adrianca@.gmail.com) writes:
> asp 6 and asp.net both took about 10 sec to create the xml client side
> from a sql table for a 50k table to xml

Just to check: how did you get the data to the client? You did get
all data into a dataset didn't you?

> sqlCommand1.CommandText = "sp_getaxml_dataisland";

sp_ is a prefix that is reserved for system stored procedure, and
SQL Server first looks in master for these. You should not use it
for your own code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Just to check: how did you get the data to the client? You did get
>all data into a dataset didn't you?
Since the resultset is just one huge SQL Text datatype,
i just wrote it directly to the page, no need for the overhead of
creating a .net dataset object,
and then a javascript dataisland reads it directly.

eg: <xml id="my-dataisland" src="http://pics.10026.com/?src=getdataisland.aspx" /
>> sqlCommand1.CommandText = "sp_getaxml_dataisland";

>sp_ is a prefix that is reserved for system stored procedure, and
>SQL Server first looks in master for these. You should not use it
>for your own code.
Thanks for the tip i didnt know that.
Do you know if first checks the master table, will that slow down the
request correct / target sp?
I had thought you needed to put "master.dbo.sp_" to access a master sp?

Thanks for your help.|||(adrianca@.gmail.com) writes:
> Since the resultset is just one huge SQL Text datatype,
> i just wrote it directly to the page, no need for the overhead of
> creating a .net dataset object,
> and then a javascript dataisland reads it directly.

Javascript is maybe not the fastest. Can you save to a file, and run a
program in a non-interpreted langauge?

> Do you know if first checks the master table, will that slow down the
> request correct / target sp?
> I had thought you needed to put "master.dbo.sp_" to access a master sp?

In such case "sp_help" would not work. In fact when you say

somedatabase.dbo.sp_help tbl

what you get information about is somedatabase.dbo.tbl.

Exactly what happens is difficult describe, because it changes every
now and then. But if Microsoft would ship a system procedure called
sp_getaxml_dataisland, you would be in for a nasty surprise.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

FOR XML query

Hello
I have a query which performs a FOR XML AUTO, ELEMENTS against my table
In my table there are some datetime fields
The resulting xml has these fields formatted as yyyy-mm-ddThh:mm:ss
(2005-03-15T15:30:00)
Is there a way to get this fields in a different format? I need dd/mm/yyyy
hh:mm:ss
ThanksTry playing around with the style parameter of CONVERT function to format
the date into a desired format.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:eNCjs$kKFHA.4092@.tk2msftngp13.phx.gbl...
> Hello
> I have a query which performs a FOR XML AUTO, ELEMENTS against my table
> In my table there are some datetime fields
> The resulting xml has these fields formatted as yyyy-mm-ddThh:mm:ss
> (2005-03-15T15:30:00)
> Is there a way to get this fields in a different format? I need dd/mm/yyyy
> hh:mm:ss
> Thanks
>
>|||Hi Denis
This is the ISO8601format for the date and therefore it is "safe", you
should look at transforming it on the client rather than in the XML.
John
"Denis" wrote:

> Hello
> I have a query which performs a FOR XML AUTO, ELEMENTS against my table
> In my table there are some datetime fields
> The resulting xml has these fields formatted as yyyy-mm-ddThh:mm:ss
> (2005-03-15T15:30:00)
> Is there a way to get this fields in a different format? I need dd/mm/yyyy
> hh:mm:ss
> Thanks
>
>

FOR XML PATH Question - Nesting Elements

Hi,
I was wondering if anyone can please help me?...I am trying to produce an
XML file using the new PATH function in SQL 2005 that has 'bullet' nodes
nested as childs of a 'bullets' element. Each bullet (to a maximum of 10)
is represented by a field in the database that is named as follows;
field_b1, field_b2, field_b3, ....etc to field_b10
I am using the below statement to produce the XML which currently only works
when I only specify 1 attribute value eg. <bullets><bullet
id="1">Parking</bullet><bullets>;
select top 1
field_id as '@.id',
field_name as 'address/name',
field_street as 'address/street',
field_town as 'address/town',
field_county as 'address/county',
field_pc as 'address/postcode',
field_price as 'price/@.value',
field_stat as 'price/status',
field_pq as 'price/qualifier',
1 as 'bullets/bullet/@.id',
field_b1 as 'bullets/bullet'
from data
where field_id = 9999999
for xml path('property'), root('info')
Which produces;
<info>
<property id="9999999">
<address>
<.... />
<.... />
etc
</address>
<price value="999999">
<... />
<... />
</price>
<bullets>
<bullet id="1">Converted Flat</bullet>
</bullets>
</property>
</info>
If I try to add ;
2 as 'bullets/bullet/@.id',
field_b2 as 'bullets/bullet'
to my statement to create the nested node with a different ID and value it
does not work. Does anyone know of a work around / solution?
Many thanks,
Pete
If I understood your problem correctly something like below should work for
you:
SELECT
1 as "bulets/bulet",
NULL as "bulets/dummy_elt",
2 as "bulets/bulet",
NULL as "bulets/dummy_elt",
3 as "bulets/bulet"
FOR XML PATH('many_bullets')
The NULL columns break the FOR XML PATH groupping logic.
This only works if you don't have "ELEMENTS XSINIL" FOR XML directive.
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:e7%23ZgLycFHA.2688@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if anyone can please help me?...I am trying to produce an
> XML file using the new PATH function in SQL 2005 that has 'bullet' nodes
> nested as childs of a 'bullets' element. Each bullet (to a maximum of
> 10) is represented by a field in the database that is named as follows;
> field_b1, field_b2, field_b3, ....etc to field_b10
> I am using the below statement to produce the XML which currently only
> works when I only specify 1 attribute value eg. <bullets><bullet
> id="1">Parking</bullet><bullets>;
> select top 1
> field_id as '@.id',
> field_name as 'address/name',
> field_street as 'address/street',
> field_town as 'address/town',
> field_county as 'address/county',
> field_pc as 'address/postcode',
> field_price as 'price/@.value',
> field_stat as 'price/status',
> field_pq as 'price/qualifier',
> 1 as 'bullets/bullet/@.id',
> field_b1 as 'bullets/bullet'
> from data
> where field_id = 9999999
> for xml path('property'), root('info')
> Which produces;
> <info>
> <property id="9999999">
> <address>
> <.... />
> <.... />
> etc
> </address>
> <price value="999999">
> <... />
> <... />
> </price>
> <bullets>
> <bullet id="1">Converted Flat</bullet>
> </bullets>
> </property>
> </info>
> If I try to add ;
> 2 as 'bullets/bullet/@.id',
> field_b2 as 'bullets/bullet'
> to my statement to create the nested node with a different ID and value it
> does not work. Does anyone know of a work around / solution?
> Many thanks,
> Pete
>
>
|||Another solution is to make the bullet generation a subquery of its own (if
you do not know a priori how many you may have).
Best regards
Michael
"Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
news:OYVV8d6cFHA.2520@.TK2MSFTNGP09.phx.gbl...
> If I understood your problem correctly something like below should work
> for you:
> SELECT
> 1 as "bulets/bulet",
> NULL as "bulets/dummy_elt",
> 2 as "bulets/bulet",
> NULL as "bulets/dummy_elt",
> 3 as "bulets/bulet"
> FOR XML PATH('many_bullets')
> The NULL columns break the FOR XML PATH groupping logic.
> This only works if you don't have "ELEMENTS XSINIL" FOR XML directive.
> Regards,
> Eugene
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Pete Roberts" <peter.roberts@.vebra.com> wrote in message
> news:e7%23ZgLycFHA.2688@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks for your help, the solutions offered are exactly what I was after!
Pete
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Od9QvEgdFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Another solution is to make the bullet generation a subquery of its own
> (if you do not know a priori how many you may have).
> Best regards
> Michael
> "Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
> news:OYVV8d6cFHA.2520@.TK2MSFTNGP09.phx.gbl...
>

FOR XML PATH NULL Element

Hi there,
I'm using sp with FOR XML PATH('Employee'), ELEMENTS to return XML Data
from SQL Server 2005.
If row return null value return xml does not return element.
Can it be returned xml element even it contains null?
Ex
i'm getting this
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
</employee>
i want this:)
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
<image/>
</employee>
*** Sent via Developersdex http://www.examnotes.net ***Hello Zoka,
You could do something like this:
SELECT
..
e.image AS "image/node()"
,'' AS "image/node()" -- Same as above :-), now "image/node()" is never
NULL :-)
..
FROM ... AS e
FOR XML PATH('employee')
HTH
/ Tobias|||
Hi there,
I tried this functionality but does not solve the problem.
*** Sent via Developersdex http://www.examnotes.net ***|||
Sorry Tobias,
This solves my problem, thanks:))
I haven't drink coffe when i first try the script:)
Regards,
Zoka
*** Sent via Developersdex http://www.examnotes.net ***

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 question

I've written some for xml explicit sql, now it works fine but what i want to
do now is add another set of elements at level 2.
For example:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
</root>
is what works fine, but now what i want is to have:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
<blah number="1"/>
<blah number="2"/>
</root>
when i write the for xml explicit to do this, it says that the element for
level 2 is already defined. or something like that. the way i see it is
that "thingy" and "blah" are both at level 2 and so should both have a tag
of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
wouldnt that make it appear under "thingy"... as (according to my book) tag
is effectively the nesting level?
i can write out full example code for you, but just for now, is there
anything obvious that i'm missing? its amazing how little resources are out
there, that talk about unusual xml explicit code!
thanks
Paul
The number you assign in the query isn't a level - it's a tag identifier. So
tags, 1 and 2 can be at the same level, they're just different tags. The
thing that determines the level is the parent, and you can assign the same
parent to as many tags as you like.
here's an example from the Northwind database
SELECT 1 As TAG, NULL As Parent,
ProductID AS [thingy!1!Number],
NULL AS [blah!2!Number]
FROM Products
UNION
SELECT 2 AS TAG, NULL AS Parent,
NULL,
CategoryID
FROM Categories
FOR XML EXPLICIT
As you'll see from the results, both thingy and blah are at the same level.
Hope that helps,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
http://www.microsoft.com/mspress/books/6137.asp
"Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...
> I've written some for xml explicit sql, now it works fine but what i want
to
> do now is add another set of elements at level 2.
> For example:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> </root>
> is what works fine, but now what i want is to have:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> <blah number="1"/>
> <blah number="2"/>
> </root>
> when i write the for xml explicit to do this, it says that the element for
> level 2 is already defined. or something like that. the way i see it is
> that "thingy" and "blah" are both at level 2 and so should both have a
tag
> of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
> wouldnt that make it appear under "thingy"... as (according to my book)
tag
> is effectively the nesting level?
> i can write out full example code for you, but just for now, is there
> anything obvious that i'm missing? its amazing how little resources are
out
> there, that talk about unusual xml explicit code!
> thanks
> Paul
>
|||Thanks! I adjusted my XML accordingly and it works, beautifully.
Its amazing how simple this all is, when you get your head round it (famous
last words until my next problem, hehe). btw I found removing the for xml
explicit part and looking at the virtual (?) table is a big help on the way
to enlightenment
Thanks again
Paul
Graeme Malcolm (Content Master Ltd.) wrote:[vbcol=seagreen]
> The number you assign in the query isn't a level - it's a tag
> identifier. So tags, 1 and 2 can be at the same level, they're just
> different tags. The thing that determines the level is the parent,
> and you can assign the same parent to as many tags as you like.
> here's an example from the Northwind database
> SELECT 1 As TAG, NULL As Parent,
> ProductID AS [thingy!1!Number],
> NULL AS [blah!2!Number]
> FROM Products
> UNION
> SELECT 2 AS TAG, NULL AS Parent,
> NULL,
> CategoryID
> FROM Categories
> FOR XML EXPLICIT
> As you'll see from the results, both thingy and blah are at the same
> level.
> Hope that helps,
> Graeme
>
> "Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
> news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...

for xml explicit or xsl

I need to create a big xml report (about 60 elements, 9 levels deep). The data is in a single sql server table. For this I have been given an xsd file that the report must match in format.
What is my best option. As far as I know, I need either a for xml explicit query or I need to create an xsl document. Is this correct?
Thanks for any advice
Asim.
At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
apply an XSLT stylesheet. I haven't got any performance data to back this
up, but that's what my gut instinct tells me.
Anyone else with any actual hard-evidence to confirm / refute this?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> I need to create a big xml report (about 60 elements, 9 levels deep). The
data is in a single sql server table. For this I have been given an xsd file
that the report must match in format.
> What is my best option. As far as I know, I need either a for xml explicit
query or I need to create an xsl document. Is this correct?
> Thanks for any advice
> Asim.
>
|||Thank you for the input.
Any pointers to where I could start creating a xslt file. Never did that. Any tools.
Can the xsd file be used in any way.
Asim.
|||Nine levels is actually not that bad for EXPLICIT mode queries from a perf
issue but hard to maintain.
Yukon's nesting capabilities would be better than either from a
programmabilty point of view.
Best regards
Michael
"Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
> At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
> apply an XSLT stylesheet. I haven't got any performance data to back this
> up, but that's what my gut instinct tells me.
> Anyone else with any actual hard-evidence to confirm / refute this?
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> "Asim" <anonymous@.discussions.microsoft.com> wrote in message
> news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> data is in a single sql server table. For this I have been given an xsd
> file
> that the report must match in format.
> query or I need to create an xsl document. Is this correct?
>
|||And to give some further perf information:
FOR XML AUTO may not provide you the right shape for postprocessing, but if
it does, the XSLT post processing will offload some of the shaping effort
from the server (thus may improve server-side throughput), but may be less
efficient end-to-end because of needing to serialize and reparse and reshape
the data (instead of shaping it directly using FOR XML explicit).
So from a performance point of view, I would think that FOR XML explicit is
in many cases more performant.
Best regards
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e48WQumIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> Nine levels is actually not that bad for EXPLICIT mode queries from a perf
> issue but hard to maintain.
> Yukon's nesting capabilities would be better than either from a
> programmabilty point of view.
> Best regards
> Michael
> "Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
> message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
>
|||Michael thats good to know that Yukon will have better nesting solution for xml. Maybe I should do this report using xml explicit and convert to Yukon as soon as the Beta is out. This report is part of a larger B2B project and doesn't go into production u
ntil August. When will the Yukon Beta be available to the general public.
Also, I have been thinking to write an "abstract" SP that can create the xmpl explicit query dynamically. Has something like this been done? Any samples.
|||You have been most helpful.
One more question. Is there a way to return multiple records from the root table in a single xml explicit query. Below is a sample script and output to illustrate what I mean. In the query, if I include a where clause (transactionID = @.ID) all is fine but
can I return multiple transactionIDs with correctly formed xml. Again thanks for your time.
create table tblXMLTest
(
transactionId int not null primary key,
quantity varchar(100) null,
)
INSERT tblXMLTest(transactionId, quantity)
select 1, '20'
INSERT tblXMLTest(transactionId, quantity)
select 2, '25'
dbcc traceon(257)
SELECT
1 as Tag,
null as Parent,
transactionId as [rootRecord!1!transactionId!element],
null as [baseSegment!2!quantity!element]
FROM tblXMLTest
union all
SELECT
2 as Tag,
1 as Parent,
null as [rootRecord!1!transactionId!element],
quantity as [baseSegment!2!quantity!element]
FROM tblXMLTest
for xml explicit
drop table tblXMLTest
The above produces the output:
<rootRecord><transactionId>1</transactionId></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>20</quantity></baseSegment><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
The desired output is:
<rootRecord><transactionId>1</transactionId><baseSegment><quantity>20</quantity></baseSegment></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
Asim.
|||The Yukon Beta should be coming out this summer. The following webpage
should allow you to nominate yourself to the beta program.
Can you send me your email alias? I then can also ask internally.
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:367BA9FB-6018-4B61-BA0A-ED6D1A56D087@.microsoft.com...
> Michael thats good to know that Yukon will have better nesting solution
> for xml. Maybe I should do this report using xml explicit and convert to
> Yukon as soon as the Beta is out. This report is part of a larger B2B
> project and doesn't go into production until August. When will the Yukon
> Beta be available to the general public.
> Also, I have been thinking to write an "abstract" SP that can create the
> xmpl explicit query dynamically. Has something like this been done? Any
> samples.
|||My email address is: asim.ahmed@.etrade.com and I work as a DBA for Etrade Financial's professional Trading division.
It would be great if I can get the Yukon Beta. By the way, your message did not include the link.
Also, I wrote a little procedure to dynamically create and run a xml explicit query, so am not worried about management as much. The only limitation is the 8000 character limit for the xml query.
I can post the script if you are interested.
Asim.
|||Thanks for the address. And sorry for not pasting the address. Here it is:
http://www.microsoft.com/sql/evaluat...ominations.asp
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:EDDFF680-C785-446E-BE87-AB93531A47B1@.microsoft.com...
> My email address is: asim.ahmed at etrade.com and I work as a DBA for
> Etrade Financial's professional Trading division.
> It would be great if I can get the Yukon Beta. By the way, your message
> did not include the link.
> Also, I wrote a little procedure to dynamically create and run a xml
> explicit query, so am not worried about management as much. The only
> limitation is the 8000 character limit for the xml query.
> I can post the script if you are interested.
> Asim.
>

FOR XML AUTO, ELEMENTS Problem

I have a column ('ProblemResolution') in a table ('Incident') that holds plain text. I am doing a query against that column and converting the results to XML as follows:

Select ProblemResolution From Incident Where RowID = 2 FOR XML AUTO, ELEMENTS

The problem is the XML that is being generated. It appears the XML that is generated is illegal (in some cases) because if I save the resulting XML in a text file and load into Internet Explorer, IE generates errors.

Here is the plain text (actually part of it - enough to demo the problem) as stored in the column. The quotes are not stored.

"8/11/2006 dabonder -
Carol –

Thanks for the detail. I looked at the 6060 transaction and it was as you thought – these accounts are not set up in the .|
If the corresponding project account to 6060 would never be used in a time sheet or expense report then you would not need to have a.
I haven't had time to clarify this. If you want to discuss when you get time I would be happy to.

-D Abonder

D Abonder
Director of Consulting
Some Company
www.SomeCompany.com

Email: dgonder@.somecompany.com
Phone: 123-555-3450"

Here is the generated (illegal) XML:

<Incident><ProblemResolution>8/11/2006 dabonder -&#x0D;
&#x0D;
Carol –&#x0D;
&#x0D;
Thanks for the detail. I looked at the 6060 transaction and it was as you thought – these accounts are not set up in the .&#x0D;
&#x0D;
If the corresponding project account to 6060 would never be used in a time sheet or expense report then you would not need to have a.&#x0D;
&#x0D;
I haven&apos;t had time to clarify this. If you want to discuss when you get time I would be happy to.&#x0D;
&#x0D;
-D Abonder&#x0D;
&#x0D;
D Abonder&#x0D;
Director of Consulting&#x0D;
Some Company&#x0D;
www.SomeCompany.com&#x0D;
&#x0D;
Email: dgonder@.somecompany.com&#x0D;
Phone: 123-555-3450</ProblemResolution></Incident>

The problem is that the document contains invalid U+0000 characters that are not allowed in XML. FOR XML does not mark them as errors but outputs them anyway. You should clean your data in the table or when running your FOR XML expression or before passing the XML to the parser and remove the U+0000 code point or replace the &#x0D; with the zero-length string.

Best regards

Michael

FOR XML AUTO, Elements and blank cell

When doing a basic select one of the SQL Server 2000 sample databasaes
with "FOR XML AUTO, Elements" the output is not well formed. Nothing
can parse it. Sometimes if there is no data in the cell it will have
an opening tag like <city> but no closing tag. Other times it will
completly eliminate data if there is nothing in it so the different
nodes in the xml all have different sets of data. How do you get SQL
Server to output the query well formed and have all the cell data in
each row in each node regardless of if it's blank or not?
Thanks.
JR"JR" <jriker1@.yahoo.com> wrote in message
news:1141948738.843007.54400@.e56g2000cwe.googlegroups.com...
> When doing a basic select one of the SQL Server 2000 sample databasaes
> with "FOR XML AUTO, Elements" the output is not well formed. Nothing
> can parse it. Sometimes if there is no data in the cell it will have
> an opening tag like <city> but no closing tag. Other times it will
> completly eliminate data if there is nothing in it so the different
> nodes in the xml all have different sets of data. How do you get SQL
> Server to output the query well formed and have all the cell data in
> each row in each node regardless of if it's blank or not?
> Thanks.
> JR
>
Can you post the query?
Joe Fawcett - XML MVP
[url]https://mvp.support.microsoft.com/profile=8AA9D5F5-E1C2-44C7-BCE8-8741D22D17A5[/ur
l]|||First FOR XML results are never guaranteed to be fully well-formed (they can
have multiple top-level nodes). You can add a root node by setting the Root
name property on your command stream object to get the result wrapped into a
document.
Secondly, you need to use the correct API to get the XML back as a stream:
The ICommandStream object in ADO or the correct API in ADO.Net to get the
XML back as a stream and not just the first 2k block...
The documentation has samples that should help you further...
Best regards
Michael
"Joe Fawcett" <joefawcett@.newsgroup.nospam> wrote in message
news:%23QJCINERGHA.2088@.TK2MSFTNGP14.phx.gbl...
> "JR" <jriker1@.yahoo.com> wrote in message
> news:1141948738.843007.54400@.e56g2000cwe.googlegroups.com...
> Can you post the query?
> --
> Joe Fawcett - XML MVP
> [url]https://mvp.support.microsoft.com/profile=8AA9D5F5-E1C2-44C7-BCE8-8741D22D17A5[/
url]
>

FOR XML AUTO, Elements and blank cell

When doing a basic select one of the SQL Server 2000 sample databasaes
with "FOR XML AUTO, Elements" the output is not well formed. Nothing
can parse it. Sometimes if there is no data in the cell it will have
an opening tag like <city> but no closing tag. Other times it will
completly eliminate data if there is nothing in it so the different
nodes in the xml all have different sets of data. How do you get SQL
Server to output the query well formed and have all the cell data in
each row in each node regardless of if it's blank or not?
Thanks.
JR
"JR" <jriker1@.yahoo.com> wrote in message
news:1141948738.843007.54400@.e56g2000cwe.googlegro ups.com...
> When doing a basic select one of the SQL Server 2000 sample databasaes
> with "FOR XML AUTO, Elements" the output is not well formed. Nothing
> can parse it. Sometimes if there is no data in the cell it will have
> an opening tag like <city> but no closing tag. Other times it will
> completly eliminate data if there is nothing in it so the different
> nodes in the xml all have different sets of data. How do you get SQL
> Server to output the query well formed and have all the cell data in
> each row in each node regardless of if it's blank or not?
> Thanks.
> JR
>
Can you post the query?
Joe Fawcett - XML MVP
https://mvp.support.microsoft.com/pr...8-8741D22D17A5
|||First FOR XML results are never guaranteed to be fully well-formed (they can
have multiple top-level nodes). You can add a root node by setting the Root
name property on your command stream object to get the result wrapped into a
document.
Secondly, you need to use the correct API to get the XML back as a stream:
The ICommandStream object in ADO or the correct API in ADO.Net to get the
XML back as a stream and not just the first 2k block...
The documentation has samples that should help you further...
Best regards
Michael
"Joe Fawcett" <joefawcett@.newsgroup.nospam> wrote in message
news:%23QJCINERGHA.2088@.TK2MSFTNGP14.phx.gbl...
> "JR" <jriker1@.yahoo.com> wrote in message
> news:1141948738.843007.54400@.e56g2000cwe.googlegro ups.com...
> Can you post the query?
> --
> Joe Fawcett - XML MVP
> https://mvp.support.microsoft.com/pr...8-8741D22D17A5
>

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

Hi,
Is it possible to include an attribute within the dataset returned using FOR
XML AUTO, ELEMENTS ?
The format returned when using ELEMENTS is perfect for what I need but I
just need to make the ID field an attribute rather than an element.
Grateful for any advice.
Alex
I am not an expert, but according to the docs you cannot do that with
AUTO. You will need to do it with RAW.
|||Actually, RAW (in SQL Server 2000) will return everything as attributes, and
as I understand it you want a mix of attributes and elements. To do that
you'd need to use EXPLICIT mode, as in this example:
USE Northwind
SELECT 1 AS Tag,
NULL AS Parent,
ProductID AS [Item!1!ProductID],
ProductName AS [Item!1!Name!element],
UnitPrice AS [Item!1!Price!element]
FROM [Products]
FOR XML EXPLICIT
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"ali" <ali.jan@.gmail.com> wrote in message
news:1121413418.618949.238900@.g14g2000cwa.googlegr oups.com...
I am not an expert, but according to the docs you cannot do that with
AUTO. You will need to do it with RAW.
|||NB: I'm assuming you're using SQL Server 2000 - in SQL Server 2005 you could
use PATH mode.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:O81hWwRiFHA.3544@.TK2MSFTNGP15.phx.gbl...
Actually, RAW (in SQL Server 2000) will return everything as attributes, and
as I understand it you want a mix of attributes and elements. To do that
you'd need to use EXPLICIT mode, as in this example:
USE Northwind
SELECT 1 AS Tag,
NULL AS Parent,
ProductID AS [Item!1!ProductID],
ProductName AS [Item!1!Name!element],
UnitPrice AS [Item!1!Price!element]
FROM [Products]
FOR XML EXPLICIT
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"ali" <ali.jan@.gmail.com> wrote in message
news:1121413418.618949.238900@.g14g2000cwa.googlegr oups.com...
I am not an expert, but according to the docs you cannot do that with
AUTO. You will need to do it with RAW.

FOR XML AUTO, ELEMENTS

I am using SQL Server 2000.
When I run SQL in Query Analyzer similar to the following, each row in the
results is truncated to 256 characters:
SELECT * FROM tblName
FOR XML AUTO, ELEMENTS
How can I prevent the output from truncating each row?
Also, is it possible for the output to be formatted with a CRLF after each
element, and appropriate indentation of elements?
Thanks
Bill
First, you can increase the limit of the result to 4000 characters. That way
you will see everything. However, since the query analyzer does not really
understand the XML, you should not attempt to use the XML there except for
doing visual checks. If you want to get the XML in a stream, use either the
ADO/ADO.Net mechanisms to get the XML stream back or use the SQLXML ISAPI.
In the later case, you would access the data through IE, and thus you would
get your pretty-printing of the XML.
Best regards
Michael
"bill" <belgie@.datamti.com> wrote in message
news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>I am using SQL Server 2000.
> When I run SQL in Query Analyzer similar to the following, each row in the
> results is truncated to 256 characters:
> SELECT * FROM tblName
> FOR XML AUTO, ELEMENTS
> How can I prevent the output from truncating each row?
> Also, is it possible for the output to be formatted with a CRLF after each
> element, and appropriate indentation of elements?
> Thanks
> Bill
>
|||I always change my settings to display 8192 instead of the annoying 256
standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
> First, you can increase the limit of the result to 4000 characters. That
> way you will see everything. However, since the query analyzer does not
> really understand the XML, you should not attempt to use the XML there
> except for doing visual checks. If you want to get the XML in a stream,
> use either the ADO/ADO.Net mechanisms to get the XML stream back or use
> the SQLXML ISAPI. In the later case, you would access the data through IE,
> and thus you would get your pretty-printing of the XML.
> Best regards
> Michael
> "bill" <belgie@.datamti.com> wrote in message
> news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>
|||Too much is not a problem. The reason why I said 4000 is that each stream
block (ie a row chunk) that is being returned is around 2034 bytes, so 4000
is enough).
But regardless of the setting, if you get the XML in more than one chunk in
the query analyzer, you will have to do some postprocessing to get rid of
the newlines.
Best regards
Mcihael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I always change my settings to display 8192 instead of the annoying 256
>standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
>
|||Yes a bit annoying that you have to do that yourself instead of checking
that "Don't give me irrelevant New-Lines"-CheckBox in QA. It would have
been nicer without this setting and that QA automatically gave you the
correct line length and that stream blocks were automatically appended as
well. I don't know any people who want them divided this way. Divided
between the tags works fine, but not like this in the middle after 2034
bytes.
I have found EmEditor to be very useful in these cases with its RegExp Find
& Replace: \r\n -> <nothing>.
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
> Too much is not a problem. The reason why I said 4000 is that each stream
> block (ie a row chunk) that is being returned is around 2034 bytes, so
> 4000 is enough).
> But regardless of the setting, if you get the XML in more than one chunk
> in the query analyzer, you will have to do some postprocessing to get rid
> of the newlines.
> Best regards
> Mcihael
> "Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
> news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
|||Well, yes. The SQL Server 2005 integration is now much better, we even have
a hyperlink triggered XML editor build in now.
So go out and upgrade :-).
Best regards
Michael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23927VrQAGHA.832@.tk2msftngp13.phx.gbl...
> Yes a bit annoying that you have to do that yourself instead of checking
> that "Don't give me irrelevant New-Lines"-CheckBox in QA. It would have
> been nicer without this setting and that QA automatically gave you the
> correct line length and that stream blocks were automatically appended as
> well. I don't know any people who want them divided this way. Divided
> between the tags works fine, but not like this in the middle after 2034
> bytes.
> I have found EmEditor to be very useful in these cases with its RegExp
> Find & Replace: \r\n -> <nothing>.
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
>

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, ELEMENTS

I am using SQL Server 2000.
When I run SQL in Query Analyzer similar to the following, each row in the
results is truncated to 256 characters:
SELECT * FROM tblName
FOR XML AUTO, ELEMENTS
How can I prevent the output from truncating each row?
Also, is it possible for the output to be formatted with a CRLF after each
element, and appropriate indentation of elements?
Thanks
BillFirst, you can increase the limit of the result to 4000 characters. That way
you will see everything. However, since the query analyzer does not really
understand the XML, you should not attempt to use the XML there except for
doing visual checks. If you want to get the XML in a stream, use either the
ADO/ADO.Net mechanisms to get the XML stream back or use the SQLXML ISAPI.
In the later case, you would access the data through IE, and thus you would
get your pretty-printing of the XML.
Best regards
Michael
"bill" <belgie@.datamti.com> wrote in message
news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>I am using SQL Server 2000.
> When I run SQL in Query Analyzer similar to the following, each row in the
> results is truncated to 256 characters:
> SELECT * FROM tblName
> FOR XML AUTO, ELEMENTS
> How can I prevent the output from truncating each row?
> Also, is it possible for the output to be formatted with a CRLF after each
> element, and appropriate indentation of elements?
> Thanks
> Bill
>|||I always change my settings to display 8192 instead of the annoying 256
standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
> First, you can increase the limit of the result to 4000 characters. That
> way you will see everything. However, since the query analyzer does not
> really understand the XML, you should not attempt to use the XML there
> except for doing visual checks. If you want to get the XML in a stream,
> use either the ADO/ADO.Net mechanisms to get the XML stream back or use
> the SQLXML ISAPI. In the later case, you would access the data through IE,
> and thus you would get your pretty-printing of the XML.
> Best regards
> Michael
> "bill" <belgie@.datamti.com> wrote in message
> news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>|||Too much is not a problem. The reason why I said 4000 is that each stream
block (ie a row chunk) that is being returned is around 2034 bytes, so 4000
is enough).
But regardless of the setting, if you get the XML in more than one chunk in
the query analyzer, you will have to do some postprocessing to get rid of
the newlines.
Best regards
Mcihael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I always change my settings to display 8192 instead of the annoying 256
>standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
>|||Yes a bit annoying that you have to do that yourself instead of checking
that "Don't give me irrelevant New-Lines"-CheckBox in QA. :) It would have
been nicer without this setting and that QA automatically gave you the
correct line length and that stream blocks were automatically appended as
well. I don't know any people who want them divided this way. Divided
between the tags works fine, but not like this in the middle after 2034
bytes.
I have found EmEditor to be very useful in these cases with its RegExp Find
& Replace: \r\n -> <nothing>.
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
> Too much is not a problem. The reason why I said 4000 is that each stream
> block (ie a row chunk) that is being returned is around 2034 bytes, so
> 4000 is enough).
> But regardless of the setting, if you get the XML in more than one chunk
> in the query analyzer, you will have to do some postprocessing to get rid
> of the newlines.
> Best regards
> Mcihael
> "Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
> news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||Well, yes. The SQL Server 2005 integration is now much better, we even have
a hyperlink triggered XML editor build in now.
So go out and upgrade :-).
Best regards
Michael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23927VrQAGHA.832@.tk2msftngp13.phx.gbl...
> Yes a bit annoying that you have to do that yourself instead of checking
> that "Don't give me irrelevant New-Lines"-CheckBox in QA. :) It would have
> been nicer without this setting and that QA automatically gave you the
> correct line length and that stream blocks were automatically appended as
> well. I don't know any people who want them divided this way. Divided
> between the tags works fine, but not like this in the middle after 2034
> bytes.
> I have found EmEditor to be very useful in these cases with its RegExp
> Find & Replace: \r\n -> <nothing>.
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
>

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...