XML Export

  • Thread starter Thread starter Phil Hunt
  • Start date Start date
P

Phil Hunt

I am able to export 2 related tables ( Order with Order detail) to XML. My
next problem is how to eliminate the key field in the XML , (like how to
eliminate the Order ID) in the output. I need that to create the join and
nesting, but I don't want it in the XML.

Thanks in advance.
 
I am not using code, although I am planning to.
To see what I am asking, just open the Northwind.mdb and try export the
Order table, there is no option to specify what field you want to export. I
tried making query of the table, that does not help. Does exporting thru
code give you more option than the gui.

Thanks
 
Using code you could specify exactly which columns you want, and even what
data. You would need to dig into the MSXML DOM in order to do it, basically
creating your own XML document in code then adding the data to it from a
Recordset. Here's a sample:

Dim db As DATABASE
Dim strSQL As String
Dim rs As Recordset
Dim oDocOut As New DOMDocument
Dim oRoot As IXMLDOMElement
Dim oNode As IXMLDOMNode
Dim oChildNode As IXMLDOMNode

Set db = DBEngine(0)(0)

strSQL = "SELECT DISTINCT LostSale.date, LostSale.partnumber,
LostSale.stocked, LostSale.description, LostSale.carline" _
& " FROM LostSale" _
& " WHERE LostSale.description Is Not Null And Len([description])>1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

oDocOut.async = False
oDocOut.resolveExternals = True

Set oNode = oDocOut.createProcessingInstruction("xml", "version='1.0'")
Set oNode = oDocOut.insertBefore(oNode, oDocOut.childNodes.Item(0))

Set oRoot = oDocOut.createElement("Root")
Set oDocOut.documentElement = oRoot
oRoot.setAttribute "xmlns:dt", "urn:schemas-microsoft-com:datatypes"

Set oNode = oDocOut.createElement("VALIDATIONID")
oNode.Text = CStr(getUserNameInternet())
oRoot.appendChild oNode

Do Until rs.EOF
Set oNode = oDocOut.createElement("LOSTSALE")
oRoot.appendChild oNode

Set oChildNode = oDocOut.createElement("SALEDATE")
oChildNode.Text = CStr(rs![Date])
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("PARTNUMBER")
oChildNode.Text = rs!partnumber
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("STOCKED")
oChildNode.Text = IIf(rs!stocked, "True", "False")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("DESCRIPTION")
oChildNode.Text = IIf(Len(rs!description) > 0, rs!description,
"None")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("CARLINE")
oChildNode.Text = IIf(Len(rs!carline) > 0, rs!carline, "None")
oNode.appendChild oChildNode

rs.MoveNext
Loop

rs.Close

At this point, I POST the XML to an ASP page. However, if you just want to
save the XML document, you would use this command:

oDocOut.save "MyXMLDoc.xml"
 
I will give it a try. Lot more coding Application.ExportXML than Access
offer.
But if I have 2 table, how does it know how to nest

thanks.


Ron Hinds said:
Using code you could specify exactly which columns you want, and even what
data. You would need to dig into the MSXML DOM in order to do it,
basically
creating your own XML document in code then adding the data to it from a
Recordset. Here's a sample:

Dim db As DATABASE
Dim strSQL As String
Dim rs As Recordset
Dim oDocOut As New DOMDocument
Dim oRoot As IXMLDOMElement
Dim oNode As IXMLDOMNode
Dim oChildNode As IXMLDOMNode

Set db = DBEngine(0)(0)

strSQL = "SELECT DISTINCT LostSale.date, LostSale.partnumber,
LostSale.stocked, LostSale.description, LostSale.carline" _
& " FROM LostSale" _
& " WHERE LostSale.description Is Not Null And
Len([description])>1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

oDocOut.async = False
oDocOut.resolveExternals = True

Set oNode = oDocOut.createProcessingInstruction("xml", "version='1.0'")
Set oNode = oDocOut.insertBefore(oNode, oDocOut.childNodes.Item(0))

Set oRoot = oDocOut.createElement("Root")
Set oDocOut.documentElement = oRoot
oRoot.setAttribute "xmlns:dt", "urn:schemas-microsoft-com:datatypes"

Set oNode = oDocOut.createElement("VALIDATIONID")
oNode.Text = CStr(getUserNameInternet())
oRoot.appendChild oNode

Do Until rs.EOF
Set oNode = oDocOut.createElement("LOSTSALE")
oRoot.appendChild oNode

Set oChildNode = oDocOut.createElement("SALEDATE")
oChildNode.Text = CStr(rs![Date])
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("PARTNUMBER")
oChildNode.Text = rs!partnumber
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("STOCKED")
oChildNode.Text = IIf(rs!stocked, "True", "False")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("DESCRIPTION")
oChildNode.Text = IIf(Len(rs!description) > 0, rs!description,
"None")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("CARLINE")
oChildNode.Text = IIf(Len(rs!carline) > 0, rs!carline, "None")
oNode.appendChild oChildNode

rs.MoveNext
Loop

rs.Close

At this point, I POST the XML to an ASP page. However, if you just want to
save the XML document, you would use this command:

oDocOut.save "MyXMLDoc.xml"


Phil Hunt said:
I am not using code, although I am planning to.
To see what I am asking, just open the Northwind.mdb and try export the
Order table, there is no option to specify what field you want to export. I
tried making query of the table, that does not help. Does exporting thru
code give you more option than the gui.

Thanks
Click to expand...
Click to expand...
 
In the Recordset loop, each time through it creates another Child Node with
the same name. This creates the "nesting", if I understand your question
correctly. It *is* a lot more coding but I think that is the only way to
achieve what you want.

Phil Hunt said:
I will give it a try. Lot more coding Application.ExportXML than Access
offer.
But if I have 2 table, how does it know how to nest

thanks.


Using code you could specify exactly which columns you want, and even what
data. You would need to dig into the MSXML DOM in order to do it,
basically
creating your own XML document in code then adding the data to it from a
Recordset. Here's a sample:

Dim db As DATABASE
Dim strSQL As String
Dim rs As Recordset
Dim oDocOut As New DOMDocument
Dim oRoot As IXMLDOMElement
Dim oNode As IXMLDOMNode
Dim oChildNode As IXMLDOMNode

Set db = DBEngine(0)(0)

strSQL = "SELECT DISTINCT LostSale.date, LostSale.partnumber,
LostSale.stocked, LostSale.description, LostSale.carline" _
& " FROM LostSale" _
& " WHERE LostSale.description Is Not Null And
Len([description])>1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

oDocOut.async = False
oDocOut.resolveExternals = True

Set oNode = oDocOut.createProcessingInstruction("xml", "version='1.0'")
Set oNode = oDocOut.insertBefore(oNode, oDocOut.childNodes.Item(0))

Set oRoot = oDocOut.createElement("Root")
Set oDocOut.documentElement = oRoot
oRoot.setAttribute "xmlns:dt", "urn:schemas-microsoft-com:datatypes"

Set oNode = oDocOut.createElement("VALIDATIONID")
oNode.Text = CStr(getUserNameInternet())
oRoot.appendChild oNode

Do Until rs.EOF
Set oNode = oDocOut.createElement("LOSTSALE")
oRoot.appendChild oNode

Set oChildNode = oDocOut.createElement("SALEDATE")
oChildNode.Text = CStr(rs![Date])
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("PARTNUMBER")
oChildNode.Text = rs!partnumber
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("STOCKED")
oChildNode.Text = IIf(rs!stocked, "True", "False")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("DESCRIPTION")
oChildNode.Text = IIf(Len(rs!description) > 0, rs!description,
"None")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("CARLINE")
oChildNode.Text = IIf(Len(rs!carline) > 0, rs!carline, "None")
oNode.appendChild oChildNode

rs.MoveNext
Loop

rs.Close

At this point, I POST the XML to an ASP page. However, if you just want to
save the XML document, you would use this command:

oDocOut.save "MyXMLDoc.xml"


Phil Hunt said:
I am not using code, although I am planning to.
To see what I am asking, just open the Northwind.mdb and try export the
Order table, there is no option to specify what field you want to
Click to expand...
export.
I
tried making query of the table, that does not help. Does exporting thru
code give you more option than the gui.

Thanks


Please post your code for doing the export.

I am able to export 2 related tables ( Order with Order detail) to
XML.
My
next problem is how to eliminate the key field in the XML , (like
Click to expand...
how
to
eliminate the Order ID) in the output. I need that to create the
Click to expand...
join
and
nesting, but I don't want it in the XML.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
In my case, we have a child node within a child node.
I think I am going to just parse my XML as text and take out the field I
don't want.

Thanks Ron.

Ron Hinds said:
In the Recordset loop, each time through it creates another Child Node
with
the same name. This creates the "nesting", if I understand your question
correctly. It *is* a lot more coding but I think that is the only way to
achieve what you want.

Phil Hunt said:
I will give it a try. Lot more coding Application.ExportXML than Access
offer.
But if I have 2 table, how does it know how to nest

thanks.


Using code you could specify exactly which columns you want, and even what
data. You would need to dig into the MSXML DOM in order to do it,
basically
creating your own XML document in code then adding the data to it from
a
Recordset. Here's a sample:

Dim db As DATABASE
Dim strSQL As String
Dim rs As Recordset
Dim oDocOut As New DOMDocument
Dim oRoot As IXMLDOMElement
Dim oNode As IXMLDOMNode
Dim oChildNode As IXMLDOMNode

Set db = DBEngine(0)(0)

strSQL = "SELECT DISTINCT LostSale.date, LostSale.partnumber,
LostSale.stocked, LostSale.description, LostSale.carline" _
& " FROM LostSale" _
& " WHERE LostSale.description Is Not Null And
Len([description])>1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

oDocOut.async = False
oDocOut.resolveExternals = True

Set oNode = oDocOut.createProcessingInstruction("xml", "version='1.0'")
Set oNode = oDocOut.insertBefore(oNode, oDocOut.childNodes.Item(0))

Set oRoot = oDocOut.createElement("Root")
Set oDocOut.documentElement = oRoot
oRoot.setAttribute "xmlns:dt", "urn:schemas-microsoft-com:datatypes"

Set oNode = oDocOut.createElement("VALIDATIONID")
oNode.Text = CStr(getUserNameInternet())
oRoot.appendChild oNode

Do Until rs.EOF
Set oNode = oDocOut.createElement("LOSTSALE")
oRoot.appendChild oNode

Set oChildNode = oDocOut.createElement("SALEDATE")
oChildNode.Text = CStr(rs![Date])
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("PARTNUMBER")
oChildNode.Text = rs!partnumber
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("STOCKED")
oChildNode.Text = IIf(rs!stocked, "True", "False")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("DESCRIPTION")
oChildNode.Text = IIf(Len(rs!description) > 0, rs!description,
"None")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("CARLINE")
oChildNode.Text = IIf(Len(rs!carline) > 0, rs!carline, "None")
oNode.appendChild oChildNode

rs.MoveNext
Loop

rs.Close

At this point, I POST the XML to an ASP page. However, if you just want to
save the XML document, you would use this command:

oDocOut.save "MyXMLDoc.xml"


I am not using code, although I am planning to.
To see what I am asking, just open the Northwind.mdb and try export
the
Order table, there is no option to specify what field you want to export.
I
tried making query of the table, that does not help. Does exporting thru
code give you more option than the gui.

Thanks


message
Please post your code for doing the export.

I am able to export 2 related tables ( Order with Order detail) to
XML.
My
next problem is how to eliminate the key field in the XML , (like how
to
eliminate the Order ID) in the output. I need that to create the join
and
nesting, but I don't want it in the XML.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Another alternative would be to use XSLT to transfrom the XML. There's an
introduction to XSLT at the following URL ...
http://msdn.microsoft.com/msdnmag/issues/0800/xslt/TOC.asp

--
Brendan Reynolds

Phil Hunt said:
In my case, we have a child node within a child node.
I think I am going to just parse my XML as text and take out the field I
don't want.

Thanks Ron.

Ron Hinds said:
In the Recordset loop, each time through it creates another Child Node
with
the same name. This creates the "nesting", if I understand your question
correctly. It *is* a lot more coding but I think that is the only way to
achieve what you want.

Phil Hunt said:
I will give it a try. Lot more coding Application.ExportXML than Access
offer.
But if I have 2 table, how does it know how to nest

thanks.


Using code you could specify exactly which columns you want, and even what
data. You would need to dig into the MSXML DOM in order to do it,
basically
creating your own XML document in code then adding the data to it from
a
Recordset. Here's a sample:

Dim db As DATABASE
Dim strSQL As String
Dim rs As Recordset
Dim oDocOut As New DOMDocument
Dim oRoot As IXMLDOMElement
Dim oNode As IXMLDOMNode
Dim oChildNode As IXMLDOMNode

Set db = DBEngine(0)(0)

strSQL = "SELECT DISTINCT LostSale.date, LostSale.partnumber,
LostSale.stocked, LostSale.description, LostSale.carline" _
& " FROM LostSale" _
& " WHERE LostSale.description Is Not Null And
Len([description])>1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

oDocOut.async = False
oDocOut.resolveExternals = True

Set oNode = oDocOut.createProcessingInstruction("xml", "version='1.0'")
Set oNode = oDocOut.insertBefore(oNode, oDocOut.childNodes.Item(0))

Set oRoot = oDocOut.createElement("Root")
Set oDocOut.documentElement = oRoot
oRoot.setAttribute "xmlns:dt",
"urn:schemas-microsoft-com:datatypes"

Set oNode = oDocOut.createElement("VALIDATIONID")
oNode.Text = CStr(getUserNameInternet())
oRoot.appendChild oNode

Do Until rs.EOF
Set oNode = oDocOut.createElement("LOSTSALE")
oRoot.appendChild oNode

Set oChildNode = oDocOut.createElement("SALEDATE")
oChildNode.Text = CStr(rs![Date])
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("PARTNUMBER")
oChildNode.Text = rs!partnumber
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("STOCKED")
oChildNode.Text = IIf(rs!stocked, "True", "False")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("DESCRIPTION")
oChildNode.Text = IIf(Len(rs!description) > 0, rs!description,
"None")
oNode.appendChild oChildNode

Set oChildNode = oDocOut.createElement("CARLINE")
oChildNode.Text = IIf(Len(rs!carline) > 0, rs!carline, "None")
oNode.appendChild oChildNode

rs.MoveNext
Loop

rs.Close

At this point, I POST the XML to an ASP page. However, if you just
want to
save the XML document, you would use this command:

oDocOut.save "MyXMLDoc.xml"


I am not using code, although I am planning to.
To see what I am asking, just open the Northwind.mdb and try export
the
Order table, there is no option to specify what field you want to export.
I
tried making query of the table, that does not help. Does exporting thru
code give you more option than the gui.

Thanks


message
Please post your code for doing the export.

I am able to export 2 related tables ( Order with Order detail) to
XML.
My
next problem is how to eliminate the key field in the XML , (like how
to
eliminate the Order ID) in the output. I need that to create the join
and
nesting, but I don't want it in the XML.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top