Sql-server – SQL to XML with multiple values

sql serverxml

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>test@test.com</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>test@test.com</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.

Best Answer

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.