By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Access 2003 XML help

P: n/a
I am trying to export an access 2003 query to XML and need to get it
to export in a very specific format. I am stuck and could use some
help badly (time is critical with this project).

This is what we have:

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="order.xsd"
generated="2004-10-26T18:32:43">
- <order>
* <order_number>24158</order_number>
* <marketchannel>TIMCUST</marketchannel>
* <Ship_Method>GRD</Ship_Method>
* <Ship_To_Name>Some_Company_Name</Ship_To_Name>
* <Ship_To_Address_1>44 SW here ave</Ship_To_Address_1>
* <Ship_To_Address_2 />
* <Ship_To_City>WORCESTER</Ship_To_City>
* <Ship_To_State>MA</Ship_To_State>
* <Ship_To_Zip>01608</Ship_To_Zip>
* <Product_ID>1096.</Product_ID>
* <UOM>EACH</UOM>
* <Product_Quantity>4</Product_Quantity>
* </order>

This is what we need:

- <Orders>
* <customer_number>111111</customer_number>
* <total_line_items>4</total_line_items>
- <Order>
* <order_number>29381274</order_number>
* <marketchannel>SOME_MARKET_CHANNEL</marketchannel>
* <Ship_Method>FDG</Ship_Method>
* <Ship_To_Name>SHIP NAME</Ship_To_Name>
* <Ship_To_Address_1>SHIP ADDRESS 1</Ship_To_Address_1>
* <Ship_To_Address_2>SHIP ADDRESS 2</Ship_To_Address_2>
* <Ship_To_City>SHIP CITY</Ship_To_City>
* <Ship_To_State>CO</Ship_To_State>
* <Ship_To_Zip>12345</Ship_To_Zip>
- <Order_Line_Item>
* <Line_number>01</Line_number>
* <Product_ID>ITEM_CODE</Product_ID>
* <UOM>EA</UOM>
* <Product_Quantity>25</Product_Quantity>
* <Rush_Code>N</Rush_Code>
* </Order_Line_Item>
- <Order_Line_Item>
* <Line_number>02</Line_number>
* <Product_ID>ITEM_CODE</Product_ID>
* <UOM>EA</UOM>
* <Product_Quantity>50</Product_Quantity>
* <Rush_Code>N</Rush_Code>
* </Order_Line_Item>
* </Order>

The problem I have is the first few lines. I need to drop the first 4
lines as well as moving <customer_number> and <total_line_items> into
an <Orders> header as shown in the second sample.

How can I achieve this or is it even possible with Access?

Any help would be great.

Thanks
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
XML is just a text file like an html file, or xsl, xsd, css, etc. Just
write out a text file the way you need it and give it a .xml extension.
You have the header section, just copy that from the wizard generated
file, then add the tags as you need based on your table column names,
and plant the data contents of each row field in between the tags (Note:
enclose your field names inside the angle brackets to create your tags -
unlikely you will need to specify CData constants and so forth).

To start with I would get a valid xml file that can be validated and is
well-formed. Copy all the xml into an Access Module as one string
variable. One long string, or several smaller strings. Write these
strings out to a text file. If you wrote the exact contents of the
original xml file to your new xml text file it should be well-formed and
should be validatable. Then you can do the same thing with your query.
Note: the query is dynamic. The xml file will be static.

This may seem tedious, but it is one possible solution.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #2

P: n/a
Mark,

1. It uses ADO
2. There's a lot of extra stuff here
3. It exports html, not xml

But, the principle is the same. Hope it gives you a start.
Darryl Kerkeslager

---------------------------------------
Private Sub btnUpdatePDA_Click()
On Error GoTo handle_error
errText = " @ Start"
Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim charCounter As Long
Dim dq As String
Dim p As Long
Dim sOff As String
Dim vOff As String
Dim safeNotes As String
Dim safeTats As String
Dim filePhoto As String
dq = Chr$(34)

errText = " @ Offenders.html"
Open odisFolder & "offenders.html" For Output As #1
Print #1, "<html><body><center>District #" & GetDistrictID() & "
Offenders</center><center>ODIS v." & GetOdisVersion() & "</center><center>"
& Now() & "</center><hr/>"
For charCounter = 65 To 90
Print #1, "<input TYPE=" & dq & "button" & dq & " VALUE=" & dq &
Chr$(charCounter) & dq & "onClick=" & dq & "window.location.href='" &
Chr$(charCounter) & ".html'" & dq & "></input>"
Next charCounter
Print #1, "<input TYPE=" & dq & "button" & dq & " VALUE=" & dq &
"Fugitives" & dq & "onClick=" & dq & "window.location.href='fugitives.html'"
& dq & "></input>"
Print #1, "</body></html>"
Close #1

For charCounter = 65 To 90
Open odisFolder & Chr$(charCounter) & ".html" For Output As #1
Print #1, "<html><body>"

With rs
.Source = "SELECT * FROM PDAQuery" & InServer() & "WHERE off_name
Like '" & Chr(charCounter) & "%' ORDER BY off_name"
.Open , cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText
errText = " @ Begin Query"
Do While Not .EOF
p = CLng(.Fields("offender_id"))
If .Fields("off_sex_offender") Then
sOff = "* Sex Offender"
Else
sOff = ""
End If
If .Fields("off_violent") Then
vOff = "* History of Violence"
Else
vOff = ""
End If
If IsNull(.Fields("off_tattoo")) Then
safeTats = ""
Else
safeTats = StrSafe(CStr(.Fields("off_tattoo")), Chr(34),
Chr(39))
End If
If IsNull(.Fields("cs_pda_notes")) Then
safeNotes = ""
Else
safeNotes = StrSafe(CStr(.Fields("cs_pda_notes")),
Chr(34), Chr(39))
End If
filePhoto = "/" & cardName & "/" & Format(Int(p / 1000),
"0000") & "/" & Format(p, "0000000") & "A.jpg"
frm.BottomMessage ("Data file: " & CStr(p) & ".html")
errText = " @ Open file"
Open odisFolder & CStr(p) & ".html" For Output As #2
Print #2, "<html><body><a name=" & dq & "T" & dq & "></a><a
href=" & dq & "#I" & dq & ">Identifiers</a> | <a href=" & dq & "#N" & dq &
">Notes</a> | <a href=" & dq & "#P" & dq & ">Photo</a><br/><br/>"
Print #2, "<b>" & RTrim(.Fields("off_name")) &
"</b><br/><hr/>"
If Len(Trim(.Fields("off_cur_location"))) > 0 Then Print #2,
..Fields("off_cur_location") & "<br/>"
Print #2, .Fields("off_address1") & "<br/>"
Print #2, .Fields("off_address2") &
"&nbsp;&nbsp;&nbsp;&nbsp;<b>Map: </b> " & .Fields("off_map_id") & "<br/>"
If Len(Trim(.Fields("off_cur_location"))) = 0 Then Print #2,
"<br/>"
Print #2, Nz(Format(.Fields("off_phone"), "(@@@) @@@-@@@@"),
"") & "<br/><br/>"
Print #2, "<b>PO: </b>" & .Fields("ppo_login") & "<br/>"
Print #2, "<b>Opened: </b>" & .Fields("cs_open_date") &
"<br/>"
Print #2, "<b>Last PC: </b>" & .Fields("cs_last_pc") &
"<br/>"
Print #2, "<b>PC Due: </b>" & .Fields("cs_pc_due") &
"<br/><br/>"
Print #2, sOff & "<br/>"
Print #2, vOff & "<br/>"
Print #2, "<b>Mental Health: </b>" & .Fields("mh_diagnosis")
& "<br/><br/><hr/>"
Print #2, "<a name=" & dq & "I" & dq & "></a>"
Print #2, .Fields("off_race") & "/" & .Fields("off_sex") &
"&nbsp;&nbsp;&nbsp;" & Format(.Fields("off_height"), "#'##") &
"&nbsp;&nbsp;&nbsp;" & .Fields("off_weight") & "lbs<br/>"
Print #2, "<b>Tattoos: </b>" & safeTats & "<br/><br/>"
Print #2, "<b>DOB: </b>" & .Fields("off_dob") & "<br/>"
Print #2, "<b>SSN: </b>" & Format(.Fields("off_ssn"),
"###-##-####") & "<br/>"
Print #2, "<b>Vaccis: </b>" & .Fields("offender_id").Value &
"<br/><br/>"
Print #2, "<a href=" & dq & "#T" & dq & ">Top</a><hr/><a
name=" & dq & "N" & dq & "></a>"
Print #2, "<b>Notes: </b>" & safeNotes & "<br/><br/><hr/>"
Print #2, "<a name=" & dq & "P" & dq & "></a>"
Print #2, "<img src=" & dq & filePhoto & dq & "><br/>"
Print #2, "<a href=" & dq & "#T" & dq &
">Top</a></body></html>"
Close #2
Print #1, "<a href=" & dq & .Fields("offender_id").Value &
".html" & dq & ">" & RTrim(.Fields("off_name")) & "</a><br>"
.MoveNext
Loop
.Close
End With
Print #1, "<br><br><a href=" & dq & "#TOP" & dq & ">Top of
Page</a></body></html>"
Close #1
Next charCounter
With rs
Open odisFolder & "fugitives.html" For Output As #3
Print #3, "<html><body>"
.Source = "SELECT offender_id, off_name FROM PDAQuery" & InServer() &
"WHERE cs_level = 'A' ORDER BY off_name"
.Open , cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText
Do While Not .EOF
Print #3, "<a href=" & dq & .Fields("offender_id").Value &
".html" & dq & ">" & RTrim(.Fields("off_name")) & "</a><br>"
.MoveNext
Loop
.Close
Print #3, "<br><br><a href=" & dq & "#TOP" & dq & ">Top of
Page</a></body></html>"
Close #3
End With
Exit Sub
handle_error:
LogError Err.Number, "FieldContactForm.btnUpdatePDA", Err.Description &
errText
End Sub
"Mark" <by*******@hotmail.com> wrote:
I am trying to export an access 2003 query to XML and need to get it
to export in a very specific format. I am stuck and could use some
help badly (time is critical with this project).

This is what we have:

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="order.xsd"
generated="2004-10-26T18:32:43">
- <order>
<order_number>24158</order_number>
<marketchannel>TIMCUST</marketchannel>
<Ship_Method>GRD</Ship_Method>
<Ship_To_Name>Some_Company_Name</Ship_To_Name>
<Ship_To_Address_1>44 SW here ave</Ship_To_Address_1>
<Ship_To_Address_2 />
<Ship_To_City>WORCESTER</Ship_To_City>
<Ship_To_State>MA</Ship_To_State>
<Ship_To_Zip>01608</Ship_To_Zip>
<Product_ID>1096.</Product_ID>
<UOM>EACH</UOM>
<Product_Quantity>4</Product_Quantity>
</order>

This is what we need:

- <Orders>
<customer_number>111111</customer_number>
<total_line_items>4</total_line_items>
- <Order>
<order_number>29381274</order_number>
<marketchannel>SOME_MARKET_CHANNEL</marketchannel>
<Ship_Method>FDG</Ship_Method>
<Ship_To_Name>SHIP NAME</Ship_To_Name>
<Ship_To_Address_1>SHIP ADDRESS 1</Ship_To_Address_1>
<Ship_To_Address_2>SHIP ADDRESS 2</Ship_To_Address_2>
<Ship_To_City>SHIP CITY</Ship_To_City>
<Ship_To_State>CO</Ship_To_State>
<Ship_To_Zip>12345</Ship_To_Zip>
- <Order_Line_Item>
<Line_number>01</Line_number>
<Product_ID>ITEM_CODE</Product_ID>
<UOM>EA</UOM>
<Product_Quantity>25</Product_Quantity>
<Rush_Code>N</Rush_Code>
</Order_Line_Item>
- <Order_Line_Item>
<Line_number>02</Line_number>
<Product_ID>ITEM_CODE</Product_ID>
<UOM>EA</UOM>
<Product_Quantity>50</Product_Quantity>
<Rush_Code>N</Rush_Code>
</Order_Line_Item>
</Order>

The problem I have is the first few lines. I need to drop the first 4
lines as well as moving <customer_number> and <total_line_items> into
an <Orders> header as shown in the second sample.

How can I achieve this or is it even possible with Access?

Any help would be great.

Thanks

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.