For Xml Path, functions and UDF's

I have created a select statement to return using for xml path which works
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this
.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UD
F
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work'
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')Ok, just found the issue with the UDF in BOL so I understand why I can't pas
s
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:

> I have created a select statement to return using for xml path which work
s
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing th
is.
> If i include the complete sub query directly in the sql it works without a
ny
> issues, but if I take the sub select and put into into a table returning U
DF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a
UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work'
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
> Ok, just found the issue with the UDF in BOL so I understand why I can't p
ass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see
if
> thay works.
> "Nathan" wrote:
>