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.
Best Answer
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 nestedFOR 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.
And there is no need to do any
NULL
checking of the input parameters via.IsNull()
since I am using theRETURNS NULL ON NULL INPUT
option:Then you can use like this:
A few easy steps to get the above SQLCLR function working (and pretty much any Assembly you create that needs
EXTERNAL_ACCESS
orUNSAFE
):The assembly needs to be signed. In Visual Studio, go to Project Properties -> SQLCLR tab -> Signing... button.
"CLR Integration" needs to be enabled:
Create an Asymmetric Key in
[master]
from the DLL:Create a Login
[master]
from the DLL:Grant the Key-based Login the appropriate permission:
Please notice how none of those steps was to turn the database property of
TRUSTWORTHY
toON
!!!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.