For XML Explicit

Question

For XML Explicit

venu_ksheerasagaram

SSCommitted

Points: 1911
More actions
October 26, 2009 at 2:20 am

#98129

Good Morning Everybody,
I need help in the following issue,
In my SP i need to get the data in XML format and it is giving like below when i used FOR XML AUTO, ELEMENTS
<BusinessRoleUserDetails>
<fkBusinessRoleID>12</fkBusinessRoleID>
<SystemConfigurations>
<SystemConfigurationValue>Admin</SystemConfigurationValue>
</SystemConfigurations>
</BusinessRoleUserDetails>
<BusinessRoleUserDetails>
<fkBusinessRoleID>13</fkBusinessRoleID>
<SystemConfigurations>
<SystemConfigurationValue>Asbestos</SystemConfigurationValue>
</SystemConfigurations>
</BusinessRoleUserDetails>
But I required the format as below:
<MainRoot>
<BusinessRoleUserDetails>
<fkBusinessRoleID>12</fkBusinessRoleID>
<SystemConfigurationValue>Admin</SystemConfigurationValue>
</BusinessRoleUserDetails>
<BusinessRoleUserDetails>
<fkBusinessRoleID>13</fkBusinessRoleID>
<SystemConfigurationValue>Asbestos</SystemConfigurationValue>
</BusinessRoleUserDetails>
</MainRoot>
How to achieve this by using FOR XML EXPLICIT
Venu Gopal.K
Software Engineer
INDIA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

NickDBA Hall of Fame Points: 3792 More actions · Answer 1

Why does you have to use EXPLICIT? I much prefer using PATH / RAW and building in subqueries to get the right structure of an XML doc.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Can you post the CREATE table statements with insert statements that insert some data and then show the XML that you want to get? This will make it much easier for anyone that wants to help you. Also why do you want to use explicit mode and not path mode which is much easier to use?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

NickDBA Hall of Fame Points: 3792 More actions · Answer 3

Yes without table defs its hard to say. Assuming the data is in one table, you could try the following to give you an XML doc based on your requirement above.

SELECT

fkBusinessRoleIDAS [fkBusinessRoleID],

SystemConfigurationValueAS [SystemConfigurationValue]

FROM [Table Name]

FOR XML PATH('BusinessRoleUserDetails'), ROOT('MainRoot')