Hello.
I need to produce:
<AppForm>
<Forename Box =1>John</Forename>
<Surname Box=2>Smith</Surname>
</AppForm>
<AppForm>
<Forename Box =1>Peter</Forename>
<Surname Box=2>Jones</Surname>
</AppForm>
Where Box is like a box number on an application form and is the same
fixed value for every record.
In my SELECT Tag = 1, Parent = NULL,
I have
NULL AS [AppForm!4!Forename!element],
NULL AS [AppForm!4!Surname!element],
And later in SELECT Tag = 4, Parent = 1,
Forename AS [AppForm!4!Forename!element],
Surname AS [AppForm!4!Surname!element],
>From tbl... etc. etc.
Thank you if you can help me.
Since the name elements have attributes, they need to be treated as
so-called entity elements, meaning that they need their own level in an
explicit mode query. I give you an example below with the EXPLICIT mode and
one with the PATH mode available in SQL Server 2005.
create table customers(custid int, fname nvarchar(100), sname nvarchar(100))
go
insert into customers
select 100, N'John', N'Smith'
union
select 200, N'Peter', N'Jones'
go
select 1 as tag, NULL as parent,
custid as "AppForm!1!!hide",
NULL as "Forename!2!Box",
NULL as "Forename!2!",
NULL as "Surname!3!Box",
NULL as "Surname!3!"
from customers
union all
select 2, 1,
custid,
1, fname,
NULL, NULL
from customers
union all
select 3, 1,
custid,
NULL, NULL,
2, sname
from customers
order by "AppForm!1!!hide", tag
for xml explicit
go
select 1 as "Forename/@.Box", fname as "Forename/text()",
2 as "Surname/@.Box", sname as "Surname/text()"
from customers
for xml path('AppForm')
HTH
Michael
<mrjjohnson@.hotmail.com> wrote in message
news:1135770606.526554.327570@.g49g2000cwa.googlegr oups.com...
> Hello.
> I need to produce:
> <AppForm>
> <Forename Box =1>John</Forename>
> <Surname Box=2>Smith</Surname>
> </AppForm>
> <AppForm>
> <Forename Box =1>Peter</Forename>
> <Surname Box=2>Jones</Surname>
> </AppForm>
> Where Box is like a box number on an application form and is the same
> fixed value for every record.
> In my SELECT Tag = 1, Parent = NULL,
> I have
> NULL AS [AppForm!4!Forename!element],
> NULL AS [AppForm!4!Surname!element],
> And later in SELECT Tag = 4, Parent = 1,
> Forename AS [AppForm!4!Forename!element],
> Surname AS [AppForm!4!Surname!element],
> Thank you if you can help me.
>
|||Michael Rys [MSFT] wrote:
> Since the name elements have attributes, they need to be treated as
> so-called entity elements, meaning that they need their own level in an
> explicit mode query. I give you an example below with the EXPLICIT mode and
> one with the PATH mode available in SQL Server 2005.
>
<snip>
Thanks very much. I'll have a look at that.
Thanks again.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment