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:
I think this is the solution you're looking for (see my comments for where I've changed your code).
For obvious reasons, I haven't tried it myself, please let me know if it doesn't work.