|
|
|
date: Thu, 12 Jun 2008 13:08:36 -0700 (PDT),
group: microsoft.public.sqlserver.xml
back
SELECT .... FOR XML and data alignment in the table
This is taken from SQL 2005 help from sp_send_dbmail article:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty
DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
If you replace email address with yours and run this query on
AdventureWorks database you will receive table in the email (assuming
you configured Database mail). All data in the table cells will be
left-aligned.
Does anyone know how to modify the above so that the data is right-
aligned?
date: Thu, 12 Jun 2008 13:08:36 -0700 (PDT)
author: unknown
Re: SELECT .... FOR XML and data alignment in the table
You should be able to use the HTML <td> element "align" attribute to get the
desired result:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT
'right' AS "td/@align",
td = wo.WorkOrderID, '',
'right' AS "td/@align",
td = p.ProductID, '',
'right' AS "td/@align",
td = p.Name, '',
'right' AS "td/@align",
td = wo.OrderQty, '',
'right' AS "td/@align",
td = wo.DueDate, '',
'right' AS "td/@align",
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr')
) AS NVARCHAR(MAX) ) +
N'</table>' ;
--
========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
wrote in message
news:dd9fa015-3b1a-41a4-a9f3-b507351b35ef@s50g2000hsb.googlegroups.com...
> This is taken from SQL 2005 help from sp_send_dbmail article:
>
> DECLARE @tableHTML NVARCHAR(MAX) ;
>
> SET @tableHTML =
> N'<H1>Work Order Report</H1>' +
> N'<table border="1">' +
> N'<tr><th>Work Order ID</th><th>Product ID</th>' +
> N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
> N'<th>Expected Revenue</th></tr>' +
> CAST ( ( SELECT td = wo.WorkOrderID, '',
> td = p.ProductID, '',
> td = p.Name, '',
> td = wo.OrderQty, '',
> td = wo.DueDate, '',
> td = (p.ListPrice - p.StandardCost) * wo.OrderQty
> FROM AdventureWorks.Production.WorkOrder as wo
> JOIN AdventureWorks.Production.Product AS p
> ON wo.ProductID = p.ProductID
> WHERE DueDate > '2004-04-30'
> AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
> ORDER BY DueDate ASC,
> (p.ListPrice - p.StandardCost) * wo.OrderQty
> DESC
> FOR XML PATH('tr'), TYPE
> ) AS NVARCHAR(MAX) ) +
> N'</table>' ;
>
> EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
> @subject = 'Work Order List',
> @body = @tableHTML,
> @body_format = 'HTML' ;
>
> If you replace email address with yours and run this query on
> AdventureWorks database you will receive table in the email (assuming
> you configured Database mail). All data in the table cells will be
> left-aligned.
>
> Does anyone know how to modify the above so that the data is right-
> aligned?
date: Sat, 14 Jun 2008 17:25:25 -0400
author: Michael Coles michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net
Re: SELECT .... FOR XML and data alignment in the table
Is there any way to format this so that i can make one of the columns a
hyperlink such that the column data being retrived is a part of the hyperlink?
the idea is that i retive an id and append that id to a url to create the
hyperlink and also display the id in the table.
"Michael Coles" wrote:
> You should be able to use the HTML <td> element "align" attribute to get the
> desired result:
>
> DECLARE @tableHTML NVARCHAR(MAX) ;
>
> SET @tableHTML =
> N'<H1>Work Order Report</H1>' +
> N'<table border="1">' +
> N'<tr><th>Work Order ID</th><th>Product ID</th>' +
> N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
> N'<th>Expected Revenue</th></tr>' +
> CAST ( ( SELECT
> 'right' AS "td/@align",
> td = wo.WorkOrderID, '',
> 'right' AS "td/@align",
> td = p.ProductID, '',
> 'right' AS "td/@align",
> td = p.Name, '',
> 'right' AS "td/@align",
> td = wo.OrderQty, '',
> 'right' AS "td/@align",
> td = wo.DueDate, '',
> 'right' AS "td/@align",
> td = (p.ListPrice - p.StandardCost) * wo.OrderQty
> FROM AdventureWorks.Production.WorkOrder as wo
> JOIN AdventureWorks.Production.Product AS p
> ON wo.ProductID = p.ProductID
> WHERE DueDate > '2004-04-30'
> AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
> ORDER BY DueDate ASC,
> (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
> FOR XML PATH('tr')
> ) AS NVARCHAR(MAX) ) +
> N'</table>' ;
>
> --
>
> ========
> Michael Coles
> "Pro SQL Server 2008 XML"
> http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
>
>
> wrote in message
> news:dd9fa015-3b1a-41a4-a9f3-b507351b35ef@s50g2000hsb.googlegroups.com...
> > This is taken from SQL 2005 help from sp_send_dbmail article:
> >
> > DECLARE @tableHTML NVARCHAR(MAX) ;
> >
> > SET @tableHTML =
> > N'<H1>Work Order Report</H1>' +
> > N'<table border="1">' +
> > N'<tr><th>Work Order ID</th><th>Product ID</th>' +
> > N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
> > N'<th>Expected Revenue</th></tr>' +
> > CAST ( ( SELECT td = wo.WorkOrderID, '',
> > td = p.ProductID, '',
> > td = p.Name, '',
> > td = wo.OrderQty, '',
> > td = wo.DueDate, '',
> > td = (p.ListPrice - p.StandardCost) * wo.OrderQty
> > FROM AdventureWorks.Production.WorkOrder as wo
> > JOIN AdventureWorks.Production.Product AS p
> > ON wo.ProductID = p.ProductID
> > WHERE DueDate > '2004-04-30'
> > AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
> > ORDER BY DueDate ASC,
> > (p.ListPrice - p.StandardCost) * wo.OrderQty
> > DESC
> > FOR XML PATH('tr'), TYPE
> > ) AS NVARCHAR(MAX) ) +
> > N'</table>' ;
> >
> > EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
> > @subject = 'Work Order List',
> > @body = @tableHTML,
> > @body_format = 'HTML' ;
> >
> > If you replace email address with yours and run this query on
> > AdventureWorks database you will receive table in the email (assuming
> > you configured Database mail). All data in the table cells will be
> > left-aligned.
> >
> > Does anyone know how to modify the above so that the data is right-
> > aligned?
>
>
>
date: Fri, 25 Jul 2008 17:55:00 -0700
author: Nikhil B S Nikhil B
Re: SELECT .... FOR XML and data alignment in the table
There's no "hyperlink" data type in SQL Server. Hyperlinks are a
client-side construct, more specifically they are an HTML concept. You can
use simple string concatenation to create an HTML tag like the following on
SQL Server (change the URL to match your needs):
'<A HREF="http://www.myurl.com?id=' + CAST(id AS varchar(20)) +
'">' + CAST(id AS varchar(20)) + '</A>';
Or you could do this on the client side (preferred method, if possible).
The end result is an HTML tag that looks like this:
<A HREF="http://www.myurl.com?id=1234">1234</A>
You can position this tag in your client web page to create a clickable
hyperlink.
--
========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X
"Nikhil B S" <Nikhil B S@discussions.microsoft.com> wrote in message
news:84F9B575-198D-4EFA-8B92-9BC9D4F6FB80@microsoft.com...
> Is there any way to format this so that i can make one of the columns a
> hyperlink such that the column data being retrived is a part of the
> hyperlink?
>
> the idea is that i retive an id and append that id to a url to create the
> hyperlink and also display the id in the table.
>
> "Michael Coles" wrote:
>
>> You should be able to use the HTML <td> element "align" attribute to get
>> the
>> desired result:
>>
>> DECLARE @tableHTML NVARCHAR(MAX) ;
>>
>> SET @tableHTML =
>> N'<H1>Work Order Report</H1>' +
>> N'<table border="1">' +
>> N'<tr><th>Work Order ID</th><th>Product ID</th>' +
>> N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
>> N'<th>Expected Revenue</th></tr>' +
>> CAST ( ( SELECT
>> 'right' AS "td/@align",
>> td = wo.WorkOrderID, '',
>> 'right' AS "td/@align",
>> td = p.ProductID, '',
>> 'right' AS "td/@align",
>> td = p.Name, '',
>> 'right' AS "td/@align",
>> td = wo.OrderQty, '',
>> 'right' AS "td/@align",
>> td = wo.DueDate, '',
>> 'right' AS "td/@align",
>> td = (p.ListPrice - p.StandardCost) * wo.OrderQty
>> FROM AdventureWorks.Production.WorkOrder as wo
>> JOIN AdventureWorks.Production.Product AS p
>> ON wo.ProductID = p.ProductID
>> WHERE DueDate > '2004-04-30'
>> AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
>> ORDER BY DueDate ASC,
>> (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
>> FOR XML PATH('tr')
>> ) AS NVARCHAR(MAX) ) +
>> N'</table>' ;
>>
>> --
>>
>> ========
>> Michael Coles
>> "Pro SQL Server 2008 XML"
>> http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
>>
>>
>> wrote in message
>> news:dd9fa015-3b1a-41a4-a9f3-b507351b35ef@s50g2000hsb.googlegroups.com...
>> > This is taken from SQL 2005 help from sp_send_dbmail article:
>> >
>> > DECLARE @tableHTML NVARCHAR(MAX) ;
>> >
>> > SET @tableHTML =
>> > N'<H1>Work Order Report</H1>' +
>> > N'<table border="1">' +
>> > N'<tr><th>Work Order ID</th><th>Product ID</th>' +
>> > N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
>> > N'<th>Expected Revenue</th></tr>' +
>> > CAST ( ( SELECT td = wo.WorkOrderID, '',
>> > td = p.ProductID, '',
>> > td = p.Name, '',
>> > td = wo.OrderQty, '',
>> > td = wo.DueDate, '',
>> > td = (p.ListPrice - p.StandardCost) * wo.OrderQty
>> > FROM AdventureWorks.Production.WorkOrder as wo
>> > JOIN AdventureWorks.Production.Product AS p
>> > ON wo.ProductID = p.ProductID
>> > WHERE DueDate > '2004-04-30'
>> > AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
>> > ORDER BY DueDate ASC,
>> > (p.ListPrice - p.StandardCost) * wo.OrderQty
>> > DESC
>> > FOR XML PATH('tr'), TYPE
>> > ) AS NVARCHAR(MAX) ) +
>> > N'</table>' ;
>> >
>> > EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
>> > @subject = 'Work Order List',
>> > @body = @tableHTML,
>> > @body_format = 'HTML' ;
>> >
>> > If you replace email address with yours and run this query on
>> > AdventureWorks database you will receive table in the email (assuming
>> > you configured Database mail). All data in the table cells will be
>> > left-aligned.
>> >
>> > Does anyone know how to modify the above so that the data is right-
>> > aligned?
>>
>>
>>
date: Sun, 10 Aug 2008 00:40:40 -0400
author: Michael Coles michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net
|
|
