breakpage in details

Hi,
I have a table with a header, footer and details. I add in my table a group
with a header and footer but without details. So the probleme i have is that
the last row of my table's detail is show on new page.
Anybody can help me to resolve that.
Thank's.
Ã?ricdid you find a solution to this. I have the same problem.
"show the last row of details on new page" wrote:
> Hi,
> I have a table with a header, footer and details. I add in my table a group
> with a header and footer but without details. So the probleme i have is that
> the last row of my table's detail is show on new page.
> Anybody can help me to resolve that.
> Thank's.
> Ã?ric

Break XML into Columns

Hi,

I have a XML of the structure,

<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello</Column>
<Column name="Header2">hello</Column>
</XMLSET>

in my database (SQL - 2k5). Now I want to split it in a manner that yields me this output;

Header Header1 Header2

Hello Hello Hello

Any pointers on how to go about this ?

Thanks in Advance,

declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'

select r.value('(./Column[@.name="Header"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Header1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Header2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

|||

Thanks for the reply....

A small query here..... Instead of hard coding the name of the column as Header, Header1 is there a way we can have the headers displayed dynamically... In short the following query will always return header, header1 and so. How to get it displayed dynamically?

select r.value('(./Column[@.name="Head"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Headr1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Headr2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

Thanks again for the reply....

|||

If you want to have dynamic column names you'll have to use dynamic SQL.

Note that you can use variables for your search criteria as below.

declare @.h1 varchar(10)
declare @.h2 varchar(10)
declare @.h3 varchar(10)

set @.h1='Header'
set @.h2='Header1'
set @.h3='Header2'

declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'

select r.value('(./Column[@.name=sql:variable("@.h1")])[1]','varchar(10)') as Header,
r.value('(./Column[@.name=sql:variable("@.h2")])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name=sql:variable("@.h3")])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

|||Thanks Mark.. That was indeed what I was looking for |||

Hi

I am processing xml in sql server 2005. The values are stored in a column as xml datatype. How to query the data that is in the column. This is the structure of the xml.

'<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber />

<level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>'

Thanks

Srini

|||

Can you be bit specific here ?

You want the data in the XML as columns is it ?

|||

The data is in the xml field of a table. The xml contains the skills of a candidate. I need to access the skill set values like CDT=false, DIT = false, and additional qualifications, if any, have to be accessed.

Thanks,

Srini

|||

Not sure if I got ur requirement clear, but I infer you wanted to break open the XML nodes as columns. Create a table like this, insert the two values and then run the query.

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)

Hope that helps...

Break XML into Columns

Hi,

I have a XML of the structure,

<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello</Column>
<Column name="Header2">hello</Column>
</XMLSET>

in my database (SQL - 2k5). Now I want to split it in a manner that yields me this output;

Header Header1 Header2

Hello Hello Hello

Any pointers on how to go about this ?

Thanks in Advance,

declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'

select r.value('(./Column[@.name="Header"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Header1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Header2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

|||

Thanks for the reply....

A small query here..... Instead of hard coding the name of the column as Header, Header1 is there a way we can have the headers displayed dynamically... In short the following query will always return header, header1 and so. How to get it displayed dynamically?

select r.value('(./Column[@.name="Head"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Headr1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Headr2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

Thanks again for the reply....

|||

If you want to have dynamic column names you'll have to use dynamic SQL.

Note that you can use variables for your search criteria as below.

declare @.h1 varchar(10)
declare @.h2 varchar(10)
declare @.h3 varchar(10)

set @.h1='Header'
set @.h2='Header1'
set @.h3='Header2'

declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'

select r.value('(./Column[@.name=sql:variable("@.h1")])[1]','varchar(10)') as Header,
r.value('(./Column[@.name=sql:variable("@.h2")])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name=sql:variable("@.h3")])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

|||Thanks Mark.. That was indeed what I was looking for |||

Hi

I am processing xml in sql server 2005. The values are stored in a column as xml datatype. How to query the data that is in the column. This is the structure of the xml.

'<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber />

<level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>'

Thanks

Srini

|||

Can you be bit specific here ?

You want the data in the XML as columns is it ?

|||

The data is in the xml field of a table. The xml contains the skills of a candidate. I need to access the skill set values like CDT=false, DIT = false, and additional qualifications, if any, have to be accessed.

Thanks,

Srini

|||

Not sure if I got ur requirement clear, but I infer you wanted to break open the XML nodes as columns. Create a table like this, insert the two values and then run the query.

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)

Hope that helps...

Break XML into Columns

Hi,

I have a XML of the structure,

<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello</Column>
<Column name="Header2">hello</Column>
</XMLSET>

in my database (SQL - 2k5). Now I want to split it in a manner that yields me this output;

Header Header1 Header2

Hello Hello Hello

Any pointers on how to go about this ?

Thanks in Advance,

declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'

select r.value('(./Column[@.name="Header"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Header1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Header2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

|||

Thanks for the reply....

A small query here..... Instead of hard coding the name of the column as Header, Header1 is there a way we can have the headers displayed dynamically... In short the following query will always return header, header1 and so. How to get it displayed dynamically?

select r.value('(./Column[@.name="Head"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Headr1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Headr2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

Thanks again for the reply....

|||

If you want to have dynamic column names you'll have to use dynamic SQL.

Note that you can use variables for your search criteria as below.

declare @.h1 varchar(10)
declare @.h2 varchar(10)
declare @.h3 varchar(10)

set @.h1='Header'
set @.h2='Header1'
set @.h3='Header2'

declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'

select r.value('(./Column[@.name=sql:variable("@.h1")])[1]','varchar(10)') as Header,
r.value('(./Column[@.name=sql:variable("@.h2")])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name=sql:variable("@.h3")])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

|||Thanks Mark.. That was indeed what I was looking for |||

Hi

I am processing xml in sql server 2005. The values are stored in a column as xml datatype. How to query the data that is in the column. This is the structure of the xml.

'<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber />

<level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>'

Thanks

Srini

|||

Can you be bit specific here ?

You want the data in the XML as columns is it ?

|||

The data is in the xml field of a table. The xml contains the skills of a candidate. I need to access the skill set values like CDT=false, DIT = false, and additional qualifications, if any, have to be accessed.

Thanks,

Srini

|||

Not sure if I got ur requirement clear, but I infer you wanted to break open the XML nodes as columns. Create a table like this, insert the two values and then run the query.

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)

Hope that helps...

Borders & Styles

Hi,
I am making a Matrix report & having a tough time getting the borders to
work.
On the column header the side borders are appearing but the top borders
phew, I dont seem to be getting them.
Please help, ThanksThe Borders don't appear on the report when do a preview but when I save it
as Excel they are there in Excel.
Any thoughts ?
Thanks
"Vishal" <vrajput77@.hotmail.com> wrote in message
news:uwPwkIdCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am making a Matrix report & having a tough time getting the borders to
> work.
> On the column header the side borders are appearing but the top borders
> phew, I dont seem to be getting them.
> Please help, Thanks
>