Dataset.writeXML having inner nodes
What I am trying to do is to output a dataset into an xml document using Dataset.WriteXML(), but one of the things that I can't figure out how to do is to have what I believe are called inner nodes.
What I mean by that is I have client data, but a client can belong to many companies, which I need to show in the xml as strings, so what I was thinking it should look like is...
Code:
<ClientData>
<Client>
<ClientName>
Test Client
</ClientName>
<Companies>
<Company>
<CompanyName>
Test Name
</CompanyName>
</Company>
</Companies>
</Client>
</ClientData>
Currently the code I have for this is as follows
Code:
Private Function produceXML(ByVal xmlQry As String, ByVal outLocation As String, ByVal dataSetName As String) As String
Dim conn As New OleDb.OleDbConnection(connectionString)
Dim da As New OleDb.OleDbDataAdapter(xmlQry, conn)
Dim ds As New DataSet
Dim fs As FileStream
Dim sw As StreamWriter
Dim outFile As String = ""
Dim blExists As Boolean = True
conn.Open()
Try
da.Fill(ds, "Client")
Catch ex As Exception
logMsg("xmlQuery was invalid, '" & xmlQry & "'")
conn.Close()
End Try
If Not outLocation.EndsWith("\") Then
outLocation &= "\"
End If
While blExists
outFile = outLocation & DateTime.Now.Ticks & ".xml"
blExists = FileExists(outFile)
End While
ds.DataSetName = dataSetName
fs = New FileStream(outFile, FileMode.Create)
sw = New StreamWriter(fs)
ds.WriteXml(sw)
sw.Close()
fs.Close()
conn.Close()
Return outFile
End Function
Hopefully someone knows how to achieve this, thanks for any help in advance.
Satal :)
Re: Dataset.writeXML having inner nodes
I thought that I would post my solution thus far.
The main problem I had was that my terminology was incorrect, I was referring to nested nodes not inner nodes.
I have added the code which I used to produce the nested nodes at the end of this post in case anyone else is interested.
The only problem I have now is that the XML isn't exactly how I would have liked, the current XML looks like this...
Code:
<Client>
<ClientID>12345</ClientID>
...
<Companies>
<LinkClientID>12345</LinkClientID>
<CompanyID>36007</CompanyID>
</Companies>
<Companies>
<LinkClientID>12345</LinkClientID>
<CompanyID>36019</CompanyID>
</Companies>
</Client>
Where as I would prefer it to look like this
Code:
<Client>
<ClientID>12345</ClientID>
...
<Companies>
<Company>
<CompanyID>36007</CompanyID>
</Company>
<Company>
<CompanyID>36007</CompanyID>
</Company>
</Companies>
</Client>
The LinkClientID is the FK in the Company table, so I would rather not show it as its just taking up space as its already known above.
Also I would have preferred each company to be in its own tag, rather than being in its own Companies tag.
Anyone have any idea's on how to solve this?
Code:
Private Function produceXML(ByVal xmlQry As String, ByVal outLocation As String, ByVal dataSetName As String) As String
'Information about Nested node from...
'http://dotnetslackers.com/articles/ado_net/MappingDataSetToXMLAndBackwards.aspx
Dim conn As New OleDb.OleDbConnection(connectionString)
Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter(xmlQry, conn)
Dim pk As DataColumn
Dim fk As DataColumn
Dim rl As DataRelation
Dim blExists As Boolean = True
Dim outFile As String = ""
da.Fill(ds, "Clients")
xmlQry = "SELECT tblDirectorDetails.LinkClientID,tblClient.ClientID As CompanyID FROM tblDirectorDetails INNER JOIN tblClient ON tblDirectorDetails.CompanyClientID=tblClient.ClientID WHERE tblDirectorDetails.LinkClientID IN (SELECT ClientID FROM tblClient WHERE ClientType=1) AND tblDirectorDetails.CompanyClientID IN (SELECT ClientID FROM tblClient WHERE ClientType=2)"
da.SelectCommand = New OleDb.OleDbCommand(xmlQry, conn)
da.Fill(ds, "Companies")
conn.Close()
pk = ds.Tables("Clients").Columns("ClientID")
fk = ds.Tables("Companies").Columns("LinkClientID")
rl = ds.Relations.Add(pk, fk)
rl.Nested = True
ds.DataSetName = dataSetName
If Not outLocation.EndsWith("\") Then
outLocation &= "\"
End If
While blExists
outFile = outLocation & DateTime.Now.Ticks & ".xml"
blExists = FileExists(outFile)
End While
'ds.WriteXmlSchema("C:\out.xsd")
ds.WriteXml(outFile, XmlWriteMode.IgnoreSchema)
Return outFile
End Function
