0

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.

  • Which RDBMS? SQL Server, given the abundance of [][][]? – Philᵀᴹ Jan 22 '16 at 18:58
  • Yes SQL Server different tables within the database – James Jan 22 '16 at 19:36
  • @James, are you sure you want all ID & Name elements under a single practice tag? Looks.. unorthodox. :) – Daniel Hutmacher Jan 22 '16 at 22:31
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.