6

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

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

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. – Solomon Rutzky Oct 2 '15 at 16:19
9

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)
{
    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.

  • 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. – Solomon Rutzky Oct 2 '15 at 16:26
3

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.