Calling all xsl/ado .net experts!

[suzy]

Hello, just a quick question which I hope has a simple answer...

I have an SP which performs an inner join on 2 tables (User and UserStatus)
and I used the dataset's .GetXml() method to return the following:

<User>
<UserId>1</UserId>
<UserStatusId>1</UserStatusId>
<UserType>1</UserType>
<UserStatusId1>1</UserStatusId1>
<UserStatusName>Logged In</UserStatusName>
</User>

As you can see, as there are 2 UserStatusId columns the 2nd column is
renamed to UserStatusId1. I know I can stop this happening by aliasing the
column names in the SP, but what I want to know is if the following can be
achieved:

<User UserId="1" UserStatusId="1" UserType="1">
<UserStatus UserStatusId="1" UserStatusName="Logged In"/>
</User>

Basically, I want the columns associated with the first table to be
attributes of the 1st node, and I want the columns of the 2nd table to be
attributes of the child node.

Is there a simple way to do this? Or is there a clever XSL transformation
that can be applied?

Many thanks.

[Christoph Schittko [MVP]]

The SP could perform a FOR XML EXPLICIT query ... which shouldn't be too
hard in your case. Look at [0] or the Books Online doc that's installed with
SQLXML.

If you app permits, you might also be able to load the two tables into the
DataSet, set the ColumnMapping Property on the DataColumns to
MappingType.Attribute and setup a DataRelation between the two tables.

--
HTH
Christoph Schittko [MVP, XmlInsider]
Software Architect, .NET Mentor

[0] http://www.topxml.com/sql/for_xml_explicit.asp

"suzy" <[e-mail]> wrote in message
news:OW**************@TK2MSFTNGP11.phx.gbl...

Hello, just a quick question which I hope has a simple answer...

I have an SP which performs an inner join on 2 tables (User and UserStatus) and I used the dataset's .GetXml() method to return the following:

<User>
<UserId>1</UserId>
<UserStatusId>1</UserStatusId>
<UserType>1</UserType>
<UserStatusId1>1</UserStatusId1>
<UserStatusName>Logged In</UserStatusName>
</User>

As you can see, as there are 2 UserStatusId columns the 2nd column is
renamed to UserStatusId1. I know I can stop this happening by aliasing the column names in the SP, but what I want to know is if the following can be
achieved:

<User UserId="1" UserStatusId="1" UserType="1">
<UserStatus UserStatusId="1" UserStatusName="Logged In"/>
</User>

Basically, I want the columns associated with the first table to be
attributes of the 1st node, and I want the columns of the 2nd table to be
attributes of the child node.

Is there a simple way to do this? Or is there a clever XSL transformation that can be applied?

Many thanks.