Output multiple XML files via DB

[benjatado]

I am attempting to create multiple XML files based upon records in SQL.

I have a combined XML that contains a Parent <Item> for each item in the SQL DB...

How can I parse each <Item> out as the <ROOT> of it's own XML file?


-Thanks!

ben

 

[JontyMC]

Need more info

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[benjatado]

Jonty-

I have a large XML file that includes multiple catalog records output from the SQL DB.
I am looking for a way to separate these results into seperate XML docs based upon the Parent level <Catalog> tag that represents each unique Catalog record and related data from the SQL DB.

I am using an SQL FOR XML AUTO, ELEMENTS to parse to XML from the DB, and this contains hundreds of catalog records.
My delima now is to somehow create multiple XML docs based upon the Parent <Catalog> element which denotes each unique record in the SQL DB.
Is there a way to seperate an XML doc based upon each iteration of the parent <Catalog>?
Or is there a way to parse multiple XML docs from SQL based upon each unique ID?
Or is this a process that I could perform through .NET after the FOR XML outputs the all the records as XML?

 

[JontyMC]

I'm not sure about doing it from SQL. But its easy to do in .Net. Something along the lines of (you may want to refine the xpath statement):

XmlDocument tempDoc = new XmlDocument();
int i = 0;
foreach(XmlNode node in xmlDoc.SelectNodes("//Catalog"))
{
  i++;
  tempDoc.RemoveAll();
  tempDoc.AppendChild(node);
  tempDoc.Save("file" + i);
}

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[benjatado]

Thanks Jon! - this is great news! because I am passing the xml to an xsl for formatting through .NET.

Just one question... where would this go within my code?

Imports System
Imports System.IO
Imports System.Xml.Schema
Imports Microsoft.Data.SqlXml 'The SQLXML managed classes

Module Module2
    Sub Main()
        'Instantiate the SqlXmlCommand object and the connection
        Dim objSqlXmlCmd As New SqlXmlCommand("Provider=SQLOLEDB;" _
            & "Server=MYSERVER;Database=MyDB;" _
            & "Integrated Security=SSPI")
       'Set type of query
        objSqlXmlCmd.CommandType = SqlXmlCommandType.Sql
        objSqlXmlCmd.CommandText = "exec TitleTrackWork"
        objSqlXmlCmd.RootTag = "Catalog"
        
        objSqlXmlCmd.XslPath = "Format.xsl"
        

        
        'execute into stream readed
        Dim objStrmReader As New StreamReader(objSqlXmlCmd.ExecuteStream())
        'prepare destiantion file
        Dim objResult As New FileStream("Result.xml", FileMode.Create)
        Dim objStrmWriter As New StreamWriter(objResult)

        'write result into destination file
        objStrmWriter.WriteLine(objStrmReader.ReadToEnd())

        objStrmWriter.Close()
        objResult.Close()
        objStrmReader.Close()
    End Sub
End Module

Thank you for your help!

 

[JontyMC]

    .......
    objStrmReader.Close()
    Dim xmlDoc As New XmlDocument
    Dim tempDoc As New XmlDocument
    Dim i As Int32 = 0

    xmlDoc.Load("Result.xml")
    For Each node As XmlNode In xmlDoc.SelectNodes("//Catalog")
      i = i + 1
      tempDoc.RemoveAll()
      tempDoc.AppendChild(tempDoc.ImportNode(node, True))
      tempDoc.Save("file" + i + ".xml")
    Next
  End Sub
End Module

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[benjatado]

Thanks Jon! -

Is the XmlNode a class within VB .net? Or included within one of the imported references?

looks interesting...especially the use of the XmlNode, .AppendChild and .SelectNodes

I will try this out!

-Thanks again for your help...here and all around the board...there is a lot to learn from guys like you!



-ben

 

[JontyMC]

All the XML stuff comes with System.Xml

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[benjatado]

Oops - An error -

Cast from string "file" to type 'Double' is not valid.

Which is related to the line.

tempDoc.Save("file" + i + ".xml")

I tried to take out the "file" string and it returned another error on the type 'Double' is not valid.

Which is another question...if I wanted to name each file from an element value in the XML or related CatalogNumber value in the SQL, could I perform this within the tempDoc.Save()? I guess I would have to establish some sort of SELECT string first, and then place that within the tempDoc.Save(selStr + ".xml") like this or something??
Would i have to re-establish a connection to the SQL, or would the one in the code above suffice?

Thank you!!