Retrieve Xml output from Sql Server 2005

Hi could someone provide me some clues or solution to retrieve record sets li开发者_如何学编程ke below

Note: I read the MSDN documentation but leads me nowhere but hair loss :(

Just for the assumption think I have 2 tables connected via Rid field

Table 1 fields,

Rid,UserName,Hash

Table2 fields

Rid,Phone,City,Email

table1 and table2 are connected via the Rid field.

I would like to have a xml output using xml auto, or xml explicit or which ever xml operations you got in SQL Server Express 2005.

Output expected:

<UserDetails>
    <Account>
        <UserName></UserName>
        <Hash></Hash>
    </Account>
    <Personal>
        <Phone1></Phone1>
        <City1></City1>
        <Phone2></Phone2>
        <City2></City2>
    </Personal>
</UserDetails>

The personal details can have n records. But account details are only one.

You could try this - what I cannot do is create those "embedded enumerations"

<Phone1></Phone1>
<City1></City1>
<Phone2></Phone2>
<City2></City2>

I don't know of any way to easily make those sequentially numbered XML tags ...

All I can do is the output I'm going to show at the end:

SELECT
    t1.UserName AS 'Account/UserName',
    t1.UserHash AS 'Account/Hash',
    (SELECT
        t2.Phone AS 'Phone',
        t2.City AS 'City'
     FROM table2 t2
     WHERE t2.Rid = t1.Rid
     FOR XML PATH(''), TYPE
    ) AS 'Personal'
FROM
    table1 t1
FOR XML PATH('UserDetails'), ROOT('Users')

This gives me an output something like:

<Users>
  <UserDetails>
    <Account>
      <UserName>xxxxx</UserName>
      <Hash>hhhhhhh</Hash>
    </Account>
    <Personal>
      <Phone>.....</Phone>
      <City>.....</City>
      <Phone>..........</Phone>
      <City>.........</City>
    </Personal>
  </UserDetails>
  <UserDetails>
    <Account>
      <UserName>cccccccccccc</UserName>
      <Hash>hhhhhhhhhhhh</Hash>
    </Account>
    <Personal>
      <Phone>................</Phone>
      <City>...........</City>
    </Personal>
  </UserDetails>
</Users>

Of course, if you want, you can add a WHERE t1.rid = xxx condition to your outer SELECT in order to limit the user and its details retrieved.