Stumped again

#1 09-04-2008, 04:48 AM

I have two tables, one holding task details and the Id of the employee
they are assigned to, and another table holding an XML representation of
an email to send to the employee telling them they have one or more
tasks assigned to them. For example:

declare @tblEmail table
(
intEmployeeId int not null,
xmlEmail XML
)

insert into @tblEmail
select 1, '<email><body></body></email>'
insert into @tblEmail
select 2, '<email><body></body></email>'

declare @tblTask table
(
intAssignedTo int,
txtTitle varchar(255)
)

insert into @tblTask
select 1, 'Task 1'
insert into @tblTask
select 1, 'Task 2'
insert into @tblTask
select 1, 'Task 3'
insert into @tblTask
select 1, 'Task 4'
insert into @tblTask
select 2, 'Task 5'

How can I perform an insert into the <body> tag of the XML column
containing details like:

Dear Employee 1, You have been assigned the following tasks: 
Task 1 
Task 2 
Task 3 
Task 4

And
Dear Employee 2, You have been assigned the following tasks: 
Task 5

Can anyone help?

#2 09-04-2008, 09:23 AM

Leon Mayne wrote:
> I have two tables, one holding task details and the Id of the employee
> they are assigned to, and another table holding an XML representation of
> an email to send to the employee telling them they have one or more
> tasks assigned to them. For example:
>
> declare @tblEmail table
> (
> intEmployeeId int not null,
> xmlEmail XML
> )
>
> insert into @tblEmail
> select 1, '<email><body></body></email>'
> insert into @tblEmail
> select 2, '<email><body></body></email>'
>
> declare @tblTask table
> (
> intAssignedTo int,
> txtTitle varchar(255)
> )
>
> insert into @tblTask
> select 1, 'Task 1'
> insert into @tblTask
> select 1, 'Task 2'
> insert into @tblTask
> select 1, 'Task 3'
> insert into @tblTask
> select 1, 'Task 4'
> insert into @tblTask
> select 2, 'Task 5'
>
> How can I perform an insert into the <body> tag of the XML column
> containing details like:
>
> Dear Employee 1, You have been assigned the following tasks: 
> Task 1 
> Task 2 
> Task 3 
> Task 4
>
> And
> Dear Employee 2, You have been assigned the following tasks: 
> Task 5
>
> Can anyone help?

The following does nearly achieve the result you want. I realize it is
not exactly what you want but maybe it helps you achieving that yourself:

declare @tblEmail table
(
intEmployeeId int not null,
xmlEmail XML
)

--insert into @tblEmail select 1, '<email><body></body></email>'
--insert into @tblEmail select 2, '<email><body></body></email>'

declare @tblTask table
(
intAssignedTo int,
txtTitle varchar(255)
)

insert into @tblTask
select 1, 'Task 1'
insert into @tblTask
select 1, 'Task 2'
insert into @tblTask
select 1, 'Task 3'
insert into @tblTask
select 1, 'Task 4'
insert into @tblTask
select 2, 'Task 5'

declare @greeting xml
set @greeting = N'Dear Employee ';
declare @t xml
set @t = N', you have been assigned the following tasks:';
declare @br xml
set @br = N' '

insert into @tblEmail
select
intAssignedTo,
(select
@greeting,
intAssignedTo as 'text()',
@t,
(select @br, txtTitle as 'text()' from @tblTask where
intAssignedTo = t1.intAssignedTo for xml path(''), type)
from @tblTask as t2
where t2.intAssignedTo = t1.intAssignedTo
group by intAssignedTo
for xml path('p'), root('email'), type)
from @tblTask as t1
group by intAssignedTo

select * from @tblEmail

Output is

1 <email>Dear Employee 1, you have been assigned the following
tasks: Task 1 Task 2 Task 3 Task 4</email>
2 <email>Dear Employee 2, you have been assigned the following
tasks: Task 5</email>

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#3 09-04-2008, 09:45 AM

Leon Mayne wrote:

> How can I perform an insert into the <body> tag of the XML column
> containing details like:
>
> Dear Employee 1, You have been assigned the following tasks: 
> Task 1 
> Task 2 
> Task 3 
> Task 4
>
> And
> Dear Employee 2, You have been assigned the following tasks: 
> Task 5
>
> Can anyone help?

Here is a second attempt:

declare @tblEmail table
(
intEmployeeId int not null,
xmlEmail XML
)

insert into @tblEmail select 1, '<email><body></body></email>'
insert into @tblEmail select 2, '<email><body></body></email>'

declare @tblTask table
(
intAssignedTo int,
txtTitle varchar(255)
)

insert into @tblTask
select 1, 'Task 1'
insert into @tblTask
select 1, 'Task 2'
insert into @tblTask
select 1, 'Task 3'
insert into @tblTask
select 1, 'Task 4'
insert into @tblTask
select 2, 'Task 5'

declare @greeting xml
set @greeting = N'Dear Employee ';
declare @t xml
set @t = N', you have been assigned the following tasks:';
declare @br xml
set @br = N' '

declare @tblMessage table
(
intEmployeeId int not null,
msg XML
)

insert into @tblMessage
select
intAssignedTo,
(select
@greeting,
intAssignedTo as 'text()',
@t,
(select @br, txtTitle as 'text()' from @tblTask where
intAssignedTo = t1.intAssignedTo for xml path(''), type)
from @tblTask as t2
where t2.intAssignedTo = t1.intAssignedTo
group by intAssignedTo
for xml path('p'), type)
from @tblTask as t1
group by intAssignedTo

select * from @tblMessage

update @tblEmail
set xmlEmail.modify(
'insert sql:column("m.msg") into (/email/body)[1]'
)
from @tblMessage as m
inner join @tblEmail as e
on m.intEmployeeId = e.intEmployeeId

select * from @tblEmail

only problem is it fails with SQL Server Express 2005 with the error
message "XQuery: SQL type 'xml' is not supported in XQuery."

I think that restriction has been removed in SQL Server 2008 so, if you
are using SQL Server 2008, then hopefully the above works for you.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#4 09-04-2008, 12:12 PM

Well it aint pretty but it does work in SQL 2005. I came up against the
XQuery / xml limitaton pretty early on.

SET NOCOUNT ON

declare @tblEmail table
(
intEmployeeId int not null,
xmlEmail XML
)

insert into @tblEmail
select 1, '<email><body></body></email>'
insert into @tblEmail
select 2, '<email><body></body></email>'

declare @tblTask table
(
intAssignedTo int,
txtTitle varchar(255)
)

insert into @tblTask
select 1, 'Task 1'
insert into @tblTask
select 1, 'Task 2'
insert into @tblTask
select 1, 'Task 3'
insert into @tblTask
select 1, 'Task 4'
insert into @tblTask
select 2, 'Task 5'

-- SELECT * FROM @tblEmail
-- SELECT * FROM @tblTask

-- Add the p element
UPDATE @tblEmail
SET xmlEmail.modify('insert Dear Employee {sql:column("intEmployeeId")}
you have been assigned the following tasks:
as first into (/email/body)[1]');

-- Work out how many loops required
DECLARE @loops INT, @i INT, @max_loops INT
SELECT @loops = MAX( records )
FROM ( SELECT COUNT(*) AS records FROM @tblTask GROUP BY intAssignedTo ) x

-- Initialise
SELECT @max_loops = 99, @i = 1

-- Loop thru
WHILE @i <= @loops
BEGIN

;WITH cte AS
(
SELECT RANK() OVER( PARTITION BY intAssignedTo ORDER BY txtTitle ) x,
intAssignedTo, txtTitle
FROM @tblTask
)
UPDATE e
SET xmlEmail.modify('insert {sql:column("t.txtTitle")}
into (/email/body/p)[1]')
FROM @tblEmail e
INNER JOIN cte t ON e.intEmployeeId = t.intAssignedTo
WHERE t.x = @i

SET @i = @i + 1

IF @i > @max_loops
BEGIN
RAISERROR( 'Too many loops.', 16, 1 )
BREAK
END
END

SELECT *
FROM @tblEmail
GO

I couldn't quite get your tags right ( I've used instead of 
) but hopefully the technique will work for you?

HTH
wBob
"Leon Mayne" wrote:

> I have two tables, one holding task details and the Id of the employee
> they are assigned to, and another table holding an XML representation of
> an email to send to the employee telling them they have one or more
> tasks assigned to them. For example:
>
> declare @tblEmail table
> (
> intEmployeeId int not null,
> xmlEmail XML
> )
>
> insert into @tblEmail
> select 1, '<email><body></body></email>'
> insert into @tblEmail
> select 2, '<email><body></body></email>'
>
> declare @tblTask table
> (
> intAssignedTo int,
> txtTitle varchar(255)
> )
>
> insert into @tblTask
> select 1, 'Task 1'
> insert into @tblTask
> select 1, 'Task 2'
> insert into @tblTask
> select 1, 'Task 3'
> insert into @tblTask
> select 1, 'Task 4'
> insert into @tblTask
> select 2, 'Task 5'
>
> How can I perform an insert into the <body> tag of the XML column
> containing details like:
>
> Dear Employee 1, You have been assigned the following tasks: 
> Task 1 
> Task 2 
> Task 3 
> Task 4
>
> And
> Dear Employee 2, You have been assigned the following tasks: 
> Task 5
>
> Can anyone help?
>

#5 09-04-2008, 12:26 PM

Bob wrote:

> UPDATE e
> SET xmlEmail.modify('insert {sql:column("t.txtTitle")}
> into (/email/body/p)[1]')

> I couldn't quite get your tags right ( I've used instead of 
> ) but hopefully the technique will work for you?

That can be fixed by using

SET xmlEmail.modify(
'insert ( ,text{sql:column("t.txtTitle")}) into
(/email/body/p)[1]')

to construct a br element plus a text node.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#6 09-04-2008, 02:21 PM

Cheers Martin!

"Martin Honnen" wrote:

> Bob wrote:
>
> > UPDATE e
> > SET xmlEmail.modify('insert {sql:column("t.txtTitle")}
> > into (/email/body/p)[1]')
>
> > I couldn't quite get your tags right ( I've used instead of 
> > ) but hopefully the technique will work for you?
>
> That can be fixed by using
>
> SET xmlEmail.modify(
> 'insert ( ,text{sql:column("t.txtTitle")}) into
> (/email/body/p)[1]')
>
> to construct a br element plus a text node.
>
> --
>
> Martin Honnen --- MVP XML
> http://JavaScript.FAQTs.com/
>

#7 09-05-2008, 04:29 AM

Thanks everyone.

#8 09-08-2008, 07:31 AM

I had another crack at this and got the following much cleaner solution:

SET NOCOUNT ON

DECLARE @tblEmail TABLE
(
intEmployeeId INT NOT NULL,
xmlEmail XML
)

INSERT INTO @tblEmail
SELECT 1, '<email><body></body></email>'
INSERT INTO @tblEmail
SELECT 2, '<email><body></body></email>'

DECLARE @tblTask TABLE
(
intAssignedTo INT,
txtTitle VARCHAR(255)
)

INSERT INTO @tblTask
SELECT 1, 'Task 1'
INSERT INTO @tblTask
SELECT 1, 'Task 2'
INSERT INTO @tblTask
SELECT 1, 'Task 3'
INSERT INTO @tblTask
SELECT 1, 'Task 4'
INSERT INTO @tblTask
SELECT 2, 'Task 5'

UPDATE @tblEmail
SET xmlEmail = x.new_xmlEmail
FROM (
SELECT e.intEmployeeId, ( SELECT txtTitle FROM @tblTask t WHERE
e.intEmployeeId = t.intAssignedTo FOR XML RAW('tasks'), ELEMENTS, TYPE
).query( '
element email
{
element body
{
element p
{
text { concat("Dear Employee ", string(sql:column("e.intEmployeeId") ),
", you have been assigned the following tasks:" ) },
for $row in (/tasks)
return
(
element br {},
text{ $row/txtTitle }
)
}
}
}' )
FROM @tblEmail e
) x ( intEmployeeId, new_xmlEmail )

SELECT *
FROM @tblEmail

"Leon Mayne" wrote:

> I have two tables, one holding task details and the Id of the employee
> they are assigned to, and another table holding an XML representation of
> an email to send to the employee telling them they have one or more
> tasks assigned to them. For example:
>
> declare @tblEmail table
> (
> intEmployeeId int not null,
> xmlEmail XML
> )
>
> insert into @tblEmail
> select 1, '<email><body></body></email>'
> insert into @tblEmail
> select 2, '<email><body></body></email>'
>
> declare @tblTask table
> (
> intAssignedTo int,
> txtTitle varchar(255)
> )
>
> insert into @tblTask
> select 1, 'Task 1'
> insert into @tblTask
> select 1, 'Task 2'
> insert into @tblTask
> select 1, 'Task 3'
> insert into @tblTask
> select 1, 'Task 4'
> insert into @tblTask
> select 2, 'Task 5'
>
> How can I perform an insert into the <body> tag of the XML column
> containing details like:
>
> Dear Employee 1, You have been assigned the following tasks: 
> Task 1 
> Task 2 
> Task 3 
> Task 4
>
> And
> Dear Employee 2, You have been assigned the following tasks: 
> Task 5
>
> Can anyone help?
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode