SQL to XML with multiple values

Question

Hello I'm new to converting SQL to XML. I have been trying to get this to work for the past few days.

SELECT Item.foldername AS "foldername", 
Item.status, 
Item.vhrid, 
Item.firstname, 
Item.middleinitial, 
Item.lastname,
dbo.getEnumDescript(Type, 'Type') AS title,
Item.email, 
dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
       (
    select  id.id 'practices/practice/ID',
           id.name'practices/practice/Name'
    from [dbo].[Groups] as aprac
    INNER JOIN [dbo].[PracticeGroups] as practices 
    on aprac.PracticeGroupID = practices.ID 
    where Item.vhrid = aprac.vhrid
    FOR XML path(''), type
)
FROM [dbo].[Att] as Item
FOR XML path, ROOT ('Item');

It returns this

<Item>
<row>
<foldername>foldername</foldername>
<status>1</status>
<vhrid>3</vhrid>
<firstname>firstname</firstname>
<lastname>lastname</lastname>
<title>title</title>
<email>[email protected]</email>
<Office>office</Office>
<practices>
  <practice>
    <ID>1</ID>
    <Name>test1</Name>
  </practice>
</practices>
<practices>
  <practice>
    <ID>2</ID>
    <Name>test2</Name>
  </practice>
</practices>
<practices>
  <practice>
    <ID>3</ID>
    <Name>test3</Name>
  </practice>
</practices>
<practices>
  <practice>
    <ID>4</ID>
    <Name>test4</Name>
  </practice>
</practices>
</row>

I'm trying to get it in this format.

<Item>
<row>
<foldername>foldername</foldername>
<status>1</status>
<vhrid>3</vhrid>
<firstname>firstname</firstname>
<lastname>lastname</lastname>
<title>title</title>
<email>[email protected]</email>
<Office>office</Office>
<practices>
  <practice>
    <ID>1</ID>
    <Name>test1</Name>
    <ID>2</ID>
    <Name>test2</Name>
    <ID>3</ID>
    <Name>test3</Name>
    <ID>4</ID>
    <Name>test4</Name>
  </practice>
</practices>
</row>

Any help would be thank full.

Answer 1

I took the liberty of assuming that you want the XML to look like this:

<row>
...
<practices>
  <practice>
    <ID>1</ID>
    <Name>test1</Name>
  </practice>
  <practice>
    <ID>2</ID>
    <Name>test2</Name>
  </practice>
  <practice>
    <ID>3</ID>
    <Name>test3</Name>
  </practice>
  <practice>
    <ID>4</ID>
    <Name>test4</Name>
  </practice>
</practices>
</row>

I think this is the solution you're looking for (see my comments for where I've changed your code).

SELECT Item.foldername AS "foldername", 
Item.status, 
Item.vhrid, 
Item.firstname, 
Item.middleinitial, 
Item.lastname,
dbo.getEnumDescript(Type, 'Type') AS title,
Item.email, 
dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
       (
    select  id.id AS [practice/ID],   --- here..
           id.name AS [practice/Name] --- here..
    from [dbo].[Groups] as aprac
    INNER JOIN [dbo].[PracticeGroups] as practices 
    on aprac.PracticeGroupID = practices.ID 
    where Item.vhrid = aprac.vhrid
    FOR XML path('/practices'), type  --- and here.
)
FROM [dbo].[Att] as Item
FOR XML path, ROOT ('Item');

For obvious reasons, I haven't tried it myself, please let me know if it doesn't work.