asked on

create an xml file with a Parent Child schema

I have the following code creating an xml file for a table i have.
table layout:
table - name "SKU_SUBMISSION"
QuoteNumber
AccountNumber
Sku
all text fields

Dim strSQL As String
    Dim rst As DAO.Recordset
    
    strSQL = "SELECT QuoteNumber, AccountNumber, Sku FROM Sku_Submission"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
   ' strPath = CurrentProject.Path & "\CategoryList.xml"
    strPath = "C:\Users\xxxx\OneDriveinc\Desktop\Escalation\EE\SkuSubmission.xml"
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim objFile As Object
    Set objFile = fso.CreateTextFile(strPath, True, True)
    
    objFile.Write "<?xml version='1.0'?>" & vbCrLf
    objFile.Write "<EsrpPriceRequest>" & vbCrLf
    
    Do Until rst.EOF
        
        objFile.Write "<QuoteNumber>" & rst.Fields("QuoteNumber") & "</QuoteNumber>" & vbCrLf
        objFile.Write "<AccountNumber>" & rst.Fields("AccountNumber") & "</AccountNumber>" & vbCrLf
        objFile.Write "<Item>" & rst.Fields("Sku") & "</Item>" & vbCrLf
      
        
        rst.MoveNext
    Loop
    
    objFile.Write "</EsrpPriceRequest>"
    
    rst.Close
    Set rst = Nothing
    
    MsgBox "Complete"

Open in new window

This Output like this:
<?xml version="1.0"?>

-<EsrpPriceRequest>

<QuoteNumber>0030017523</QuoteNumber>

<AccountNumber>0800000713</AccountNumber>

<Sku>3BA56</Sku>

<QuoteNumber>0030017523</QuoteNumber>

<AccountNumber>0800000713</AccountNumber>

<Sku>1A005</Sku>

<QuoteNumber>0030017523</QuoteNumber>

<AccountNumber>0800000713</AccountNumber>

<Sku>1AC47</Sku>

<QuoteNumber>0030017523</QuoteNumber>

<AccountNumber>0800000713</AccountNumber>

<Sku>6K305</Sku>

</EsrpPriceRequest>
with Items and Item Parent Child

I need the xml to look like this:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<EsrpPriceRequest>

<QuoteNumber>0030017523</QuoteNumber>

<AccountNumber>0800000713</AccountNumber>


-<Items>

<Item>3BA56</Item>

<Item>1A005</Item>

<Item>1AC47</Item>

<Item>6K305</Item>

</Items>

</EsrpPriceRequest>

Open in new window

Thanks
fordraiders

Ryan Chong

try this:

    Dim QuoteNumber As String
   
    Dim strSQL As String
    Dim rst As DAO.Recordset
   
    strSQL = "SELECT QuoteNumber, AccountNumber, Sku FROM Sku_Submission"
   
    Set rst = CurrentDb.OpenRecordset(strSQL)
   
   ' strPath = CurrentProject.Path & "\CategoryList.xml"
    strPath = "C:\Users\xxxx\OneDriveinc\Desktop\Escalation\EE\SkuSubmission.xml"
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    Dim objFile As Object
    Set objFile = fso.CreateTextFile(strPath, True, True)
   
    objFile.Write "<?xml version='1.0'?>" & vbCrLf
    objFile.Write "<EsrpPriceRequest>" & vbCrLf
   
    Do Until rst.EOF
        If rst.Fields("QuoteNumber") <> QuoteNumber Then
            objFile.Write "<QuoteNumber>" & rst.Fields("QuoteNumber") & "</QuoteNumber>" & vbCrLf
            objFile.Write "<AccountNumber>" & rst.Fields("AccountNumber") & "</AccountNumber>" & vbCrLf
            QuoteNumber = rst.Fields("QuoteNumber")
        End If
       
        objFile.Write "<Item>" & rst.Fields("Sku") & "</Item>" & vbCrLf
       
        rst.MoveNext
    Loop
   
    objFile.Write "</EsrpPriceRequest>"
   
    rst.Close
    Set rst = Nothing
   
    MsgBox "Complete"

Open in new window

Fordraiders

ASKER

amended to this :
I need the PARENT nodes

Do Until rst.EOF
If rst.Fields("QuoteNumber") <> QuoteNumber Then
objFile.Write "<QuoteNumber>" & rst.Fields("QuoteNumber") & "</QuoteNumber>" & vbCrLf
objFile.Write "<AccountNumber>" & rst.Fields("AccountNumber") & "</AccountNumber>" & vbCrLf
QuoteNumber = rst.Fields("QuoteNumber")
objFile.Write "<Items>" & vbCrLf
End If

objFile.Write "<Item>" & rst.Fields("Sku") & "</Item>" & vbCrLf

rst.MoveNext
Loop
objFile.Write "</Items>" & vbCrLf
objFile.Write "</EsrpPriceRequest>"

rst.Close
Set rst = Nothing

Fordraiders

ASKER

one weird thing.
trying to change this:
objFile.Write "<?xml version='1.0'?>" & vbCrLf

to

objFile.Write "<?xml version='1.0' encoding='UTF-8' standalone='true'?>" & vbCrLf

will not work ?

fordraiders

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

ryan, its creating xml correctly..
I verified.

this part for the xml version will not work

trying to change this:
objFile.Write "<?xml version='1.0'?>" & vbCrLf

to

objFile.Write "<?xml version='1.0' encoding='UTF-8' standalone='true'?>" & vbCrLf

will not work ?

Ryan Chong

can you share the generated XML content here?

John Tsioumpris

Is much better and safer to work in a way to produce properly structured XMLs with Nodes and not tamper with text manipulation
Take a look here for an example : http://trypots.net/srv/vb-xmldoc.html
As Sidenote always check your Xmls with an editor that supports XML like Notepad++