• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

SQL for XML function to file

I currently am using the function below to deliver a bill of material from the given part number to an XML file. As the function stands, I have to manually save the xml file.

I want to develop a VB windows program so that the end user can export the XML BOM for other uses.

What is the best way to attack this? Is there a way to do it in the function? Or should this be handled from the VB side? If so, How?

Any help would be greatly appreciated.



ALTER FUNCTION [dbo].[GetPartsSubTree](@PartNumberID nvarchar(50))
RETURNS XML
BEGIN RETURN
(SELECT CorpComponent AS "@ID",
        Description AS "Description",
            Drawing as "Drawing",
            EBOMParent as "Parent",
            Qty as "Qty",
            NSN as "NSN",
        dbo.GetPartsSubTree(CorpComponent)
FROM FRM777A1MBOMB4
WHERE EBOMParent=@PartNumberID
ORDER BY CorpComponent
FOR XML PATH('PART'),ROOT('SUBPARTS'),TYPE)
END

0
phil301
Asked:
phil301
  • 5
  • 5
1 Solution
 
CodeCruiserCommented:
You would write a simple and small VB app which would call this function using ADO.NET. Then you can simple save the returned xml into a file.
0
 
phil301Author Commented:
Thanks for the reply. I have written the VB app as below. It seems to work, but the file is now a text file with an XML extension. If I view the file, it is no longer using an "XML" type format, but rather a bunch of jumbled text.

Any ideas on how to keep or convert it back to "XML" format?

Dim strSQL2 As String
        Dim Connection As New SqlConnection(connectionstring11)
        Dim myfile As String
        Connection.Open()
        strSQL2 = "select dbo.getPartsSubTree (" & Me.TextBox1.Text & ")"
        Dim Com2 As New System.Data.SqlClient.SqlCommand(strSQL2, Connection)
        myfile = Com2.ExecuteScalar
        Connection.Close()
        Dim dir1 As String
        dir1 = "c:\XMLExport"
        If System.IO.Directory.Exists(dir1) Then
            GoTo 20
        Else
            Directory.CreateDirectory(dir1)
        End If
20:     Dim oWrite As System.IO.StreamWriter        
        oWrite = IO.File.CreateText(dir1 & "\" & Me.TextBox1.Text & ".xml")
        oWrite.WriteLine(myfile)
        oWrite.Close()
0
 
CodeCruiserCommented:
I dont think this is the right way of retreiving and saving xml. Is the XML being returned as string or byte array from the db? Try using the following code
Dim strSQL2 As String
Dim Connection As New SqlConnection(connectionstring11)
Connection.Open()
strSQL2 = "select dbo.getPartsSubTree (" & Me.TextBox1.Text & ")"
Dim Com2 As New System.Data.SqlClient.SqlCommand(strSQL2, Connection)
dim dbreader as datareader = Com2.ExecuteReader
dbreader.read
dim MyData as string = dbreader.item(0)
dbreader.close()
Connection.Close()
Dim dir1 As String
dir1 = "c:\XMLExport"
dim MyFile as string = dir1 & "\MyXML.xml"
If Not System.IO.Directory.Exists(dir1) Then
   Directory.CreateDirectory(dir1)
End If
IO.File.WriteAllText(myfile, MyData)

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
phil301Author Commented:
Thanks for that. That is probably a better way of retrieveing the data. However, the result appear to be the same. If I open the file in notepad, dreamweaver or an xml editor, its still jumbled text. If I run the query directly in sql management studio, it opens fine in these programs.

I guess this is not really an issue, after further testing Excel and IE seem to have no problem with it. It opens in the "XML" format as expected.
0
 
CodeCruiserCommented:
Can you upload the generated file?
0
 
phil301Author Commented:
Unfortunately, I can not do that. It is an ITAR controlled document. I can however just post the structure if that would help.
0
 
CodeCruiserCommented:
No its ok. I wanted to see what is being generated as you said it was unreadable text but then excel and IE are able to open and display it. When you said jumbled text, what did you mean?
0
 
phil301Author Commented:
It simply just like one long sentence. ie....  <Subparts><Parts><Id><NSN><Subparts><Parts><Id><NSN><Subparts><Parts><Id><NSN><Subparts><Parts><Id><NSN><Subparts><Parts><Id><NSN><Subparts><Parts><Id><NSN><Subparts><Parts><Id><NSN><Subparts><Parts><Id><NSN><Subparts><Parts><Id><NSN>....
0
 
CodeCruiserCommented:
Yeah but it is valid xml. It is being returned this way from the SQL Server. You can open it in notepad and do wordwrap. There is no identation done by SQL Server.
0
 
phil301Author Commented:
Thank you very much
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now