Exporting results to XML

Question

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

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

SELECT
a.[heading_id] as BranchID,
c.[name] as BranchName,
a.[business_id] as SubBranchID, 
a.[heading_id] as SubParentBranchID,
b.[name] as SubBranchName
   FROM [BUSINESSHEADINGLINK] as a
   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

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:

Can anyone help me with the SQL code?

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

Answer 1

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:

• Use EXPLICIT Mode with FOR XML
• Use Nested FOR XML Queries

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)
{
    try
    {
        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)
RETURNS NVARCHAR(4000)
WITH EXECUTE AS CALLER,
     RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SomeAssemblyName].[FileUtils].[SaveXmlToFile];

Then you can use like this:

DECLARE @Output XML;

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;
   RECONFIGURE;
   

3. Create an Asymmetric Key in [master] from the DLL:

   USE [master];
   CREATE ASYMMETRIC KEY [KeyName]
   FROM EXECUTABLE FILE = 'Path\to\SomeAssemblyName.dll';
   

4. Create a Login [master] from the DLL:

   CREATE LOGIN [SomeLoginName]
   FROM ASYMMETRIC KEY [KeyName];
   

5. Grant the Key-based Login the appropriate permission:

   GRANT EXTERNAL ACCESS ASSEMBLY TO [SomeLoginName];
   

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.

Answer 2

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