I am trying to create an XML file from a SQL query. The out put should look like the image below

enter image description here

Where the tag BranchID appears again as SubParentBranchID if there are more than one SubBranchID associated with BranchID. In the image below BranchID 94 has two SubBranchID 63 and 64.

I've manged this SQL query

a.[heading_id] as BranchID,
c.[name] as BranchName,
a.[business_id] as SubBranchID, 
a.[heading_id] as SubParentBranchID,
b.[name] as SubBranchName
   join [BUSINESS] as b on a.business_id = b.business_id
   join [HEADING] as c on   a.heading_id = c.heading_id 
FOR XML PATH ('Branch'), ROOT('BranchInfo')

This gives me the following XML

enter image description here

Here you can see that neither do i have the tag <SubBranches> nor <SubBranch>. Also i do not have <SubParentBranchID>. The XML file should be like:

enter image description here

Can anyone help me with the SQL code?

Also i would like to save it on C:/temp after execution.

  • Try using ELEMENTS e.g. FOR XML PATH ('Branch'), ROOT('BranchInfo'), ELEMENTS to see you get the desired output.
    – Kin Shah
    Oct 2 '15 at 14:55
  • I did that but no use
    – itsAftab
    Oct 2 '15 at 15:55
  • @Kin and itsAftab: ELEMENTS is not the issue here. The issue is producing a multi-level structure as opposed to the default flat structure. And that can only be done by using the two methods I mention in Part 1 of my answer. Oct 2 '15 at 16:19

Part 1: Proper XML Struture

In order to get anything other than a flat XML layout, you need to use either FOR XML EXPLICIT mode, or nested FOR XML AUTO queries. Please see the following MSDN sections for complete details, including examples:

Also, you do not need to generate the <SubParentBranchID> element as it is entirely superfluous. One of the nice things about XML is being able to get the parent node from your current location :).

Part 2: Exporting to a File

This can be done rather easily with a simple SQLCLR function to save the contents of a variable (or query) into a text file.

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = false, IsPrecise = true)]
public static SqlString SaveXmlToFile([SqlFacet(MaxSize = 4000)] SqlString FilePath,
    SqlXml XmlData)
        File.WriteAllText(FilePath.Value, XmlData.Value, Encoding.Unicode);
    catch (Exception __Exception)
        return __Exception.Message;

    return String.Empty;

And there is no need to do any NULL checking of the input parameters via .IsNull() since I am using the RETURNS NULL ON NULL INPUT option:

CREATE FUNCTION [dbo].[SaveXmlToFile](@FilePath NVARCHAR(4000), @XmlData XML)
AS EXTERNAL NAME [SomeAssemblyName].[FileUtils].[SaveXmlToFile];

Then you can use like this:


SET @Output = (
   SELECT ...
   FOR XML ...;

DECLARE @ErrorMessage NVARCHAR(4000);
SET @ErrorMessage = dbo.SaveXmlToFile(N'path/to/file.xml', @Output);

A few easy steps to get the above SQLCLR function working (and pretty much any Assembly you create that needs EXTERNAL_ACCESS or UNSAFE):

  1. The assembly needs to be signed. In Visual Studio, go to Project Properties -> SQLCLR tab -> Signing... button.

  2. "CLR Integration" needs to be enabled:

    EXEC sp_configure 'clr enabled', 1;
  3. Create an Asymmetric Key in [master] from the DLL:

    USE [master];
    FROM EXECUTABLE FILE = 'Path\to\SomeAssemblyName.dll';
  4. Create a Login [master] from the DLL:

    CREATE LOGIN [SomeLoginName]
  5. Grant the Key-based Login the appropriate permission:


Please notice how none of those steps was to turn the database property of TRUSTWORTHY to ON!!!

An alternate means of getting this SQLCLR function without doing any coding, compiling, creating of Asymmetric Keys or Logins, etc, is to get a pre-done library that you just install. The SQL# library contains several File System functions and bypasses all of the steps shown above. Please note that I am the author of SQL#, and while there is a Free version, the File System functions are only available in the Full version.

  • that is too complicated
    – itsAftab
    Oct 2 '15 at 15:58
  • 5
    What exactly is too complicated? There are two parts here. You don't have much choice about Part 1 if you want to produce multi-level XML natively in T-SQL. For Part 2, that could be made much easier by simply installing a pre-done SQLCLR function. And I do sell a package that contains such a function (and over 250 others) called SQL#. There is a Free version, but the File System functions are only in the Full version. Oct 2 '15 at 16:26

If your structure is only two levels deep you can use one nested for xml query to build the sub branch part something like this.

select H.heading_id as 'BranchID',
       H.name as 'BranchName',
       select B.business_id as 'SubBranchID',
              H.heading_id as 'SubParentBrachID',
              B.name as 'SubBranchName'
       from dbo.BUSINESS as B
         inner join dbo.BUSINESSHEADINGLINK as L
           on B.business_id = L.business_id
       where L.heading_id = H.heading_id
       for xml path('SubBranch'), root('SubBranches'), type
from dbo.HEADING as H
for xml path('Branch'), root('BranchInfo'), type;

SQL Fiddle

  • thank you for the suggestion..though when i try to save it as XML file on local disk using queryout (C:\temp\test.xml) i get an error: Incorrect syntax near 'queryout'
    – itsAftab
    Oct 5 '15 at 11:23
  • so i managed to save it but the file is all in one line and without breaks..how do i insert breaks after every node?
    – itsAftab
    Oct 6 '15 at 14:55

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.