Creating an XML file with a specific schema

Hi Papusale

u have some blank value in Product_ID_Required column

so U mean to say for blank also u want me to show in XML ? is it right , if so I will be back in 30 minutes

hi Papusale

your data has some invalid name like (ä & ö & å) and I have removed invalid letters from some rows and tried & its worked perfectly(you can find data in sheet1 on which I run the macro)

please find the code below & attached file

Sub XMLFIle()
  Dim strXML As String
    'strXML = NavfGenerateXML(Selection, "IndividualAccounts")
    Dim HEADER As String
    Dim TAG_BEGIN  As String
    Dim TAG_END  As String
    Dim LC As Long
    Dim LR As Long
    Dim Btag As String
    Dim filenameinput As String
    Dim FPath As String, FName As String
'========================================
Dim Sht As Worksheet: Set Sht = ThisWorkbook.Sheets("Sheet1")
FPath = "C:\Users\NaveedM\Downloads\Excel Forum" '<--- change the path suit to you
FName = "XMLTest" '<--- change the name suit to you
filenameinput = FPath & "\" & FName & ".xml"


HEADER = "<?xml version=""1.0"" encoding=""UTF-8"" ?>" & vbCrLf
strXML = HEADER
 
    
   TAG_BEGIN = "<Products>"
   TAG_END = "</Products>"

    strXML = strXML & TAG_BEGIN
    
    With Sht
        'Finding Last Row
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        LC = 7
                
        For i = 2 To LR
            strXML = strXML & vbCrLf & "<Product>"
            
            For j = 1 To LC
                If .Cells(i, j).Value = "" Then
                    strXML = strXML & vbCrLf & "<" & .Cells(2, j).Value & "/>"
                Else
                    strXML = strXML & vbCrLf & "<" & .Cells(1, j).Value & ">" & .Cells(i, j).Value & "</" & .Cells(1, j).Value & ">"
                End If
            Next
            j = 1
            strXML = strXML & vbCrLf & "</Product>"
        Next
   End With
   strXML = strXML & TAG_END
'=========================================
    sWriteFile strXML, filenameinput
    MsgBox ("Completed. XML Written to " & filenameinput)
End Sub
' Function for writing plain string out a file
Sub sWriteFile(strXML As String, strFullFileName As String)

    Dim intFileNum As String
    
    intFileNum = FreeFile
    Open strFullFileName For Output As #intFileNum
    Print #intFileNum, strXML
    Close #intFileNum
End Sub

Thank you very much Naveed!

Actually the (ä & ö & å) characters are not invalid, they are supposed to be there, as these are characters in Finnish language.
Is it possible to create the code to also support these?

Also, how do you actually create the XML file?

I opened your modified excel file version and tried to save as XML file (data), and it says:
"Cannot save XML data because the workbook does not contain any XML mappings."

Again, thank you for everything

Steps:

1. Just the Open the Excel file
2. Press Alt+F11 and double click on Module 1 and change the Path in script page suit to u
3. Next, go back to excel file & press Alt + F8 and select XMLFile and click on Run

it will create a XML file in that path which u have assign

Hi Papusale

just make that attached file as your Macro Tool..

1. Just copy your original Data and dump in that attached file (Sheet1 Tab) as per header.
2. don't rename the 1st tab.
3. Next run the macro that's it

Note: that Invalid Letters should not be there if there then xml file will not create properly

if you got your solution please mark the spread as SOLVED and click on * Add Reputation to say thanks don't forget

Hi Papusale

just make that attached file as your Macro Tool..

1. Just copy your original Data and dump in that attached file (Sheet1 Tab) as per header.
2. don't rename the 1st tab.
3. Next run the macro that's it

Note: that Invalid Letters should not be there if there then xml file will not create properly

if you got your solution please mark the spread as SOLVED and click on * Add Reputation to say thanks don't forget

Thanks for the Reputation

yeah it will automatically generate XML file just u need to dump your data in that attached file in Sheet1 Tab and run the macro that's it, it will generate automatically ....

You mean to say you have added new two more column right , if its so then replace below code

LC = 7

with this

LC = .Cells(1, .Columns.Count).End(xlToLeft).Column

Hi Naveed,

So it seems that the module does not generate the XML correctly when I insert the full element list for the XML.

Attached is the excel file for which I want to generate the XML.

It seems that other special characters (such as "&") are not allowed. However these characters are necessary, since they are included in some URLs etc.

Hi Papusale

please find the updated version

please assign Fpath otherthan C drive and try , and one more thing those cells is blank for that u will get only close bracket in XML file

please have test and let me know

tl;dr:

1) have you tried xml add-in? Your xml schema has a very simple structure, so I would suggest use this add-in.

Just follow steps at http://bitwizards.com/blog/november-...rksheet-to-xml. It's much more convenient than macro, because any change in schema can be easily reflected in the map (xsd file) and you don't have to go through the VBA code.


2) This can be also solved by formulas:

="<Product>"&if(A1="","<Product_ID_Required/>","<Product_ID_Required>"&A1&"</Product_ID_Required>")&if(B1="","<Title_Required/>","<Title_Required>"&B1&"</Title_Required>")&...

fill for the first row and then only expand down.

Originally Posted by papusale

I have tried the add in before, but since I'm not very savvy with Excel, I didn't know how to create one with my XML schema. Could you help perhaps?

Sure. Fill

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="NewsItemsTable">
<xs:complexType>
<xs:sequence>
<xs:element ref="NewsItemRow" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NewsItemRow">
<xs:complexType>
<xs:sequence>
<xs:element name="Product_ID_Required" type="xs:string"/>
<xs:element name="Title_Required" type="xs:string"/>
<xs:element name="..." type="xs:string"/>
<xs:element name="..." type="xs:string"/>
<xs:element name="..." type="xs:string"/>
<xs:element name="..." type="xs:string"/>
<xs:element name="..." type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

with your elements (column names). Then you save the file as map.xsd. It is important that the order of elements is same as order of columns in your worksheet. Once you have the add-in installed in Excel, click on Source, choose the xsd file, right click on the map in Excel, choose "Map Element", highlight your table with the header, click ok and export as xml file. Then you have you file.



Basically, create a worksheet with data as at http://bitwizards.com/blog/november-...rksheet-to-xml and follow all the steps. Reproduce what they have done. Once you manage to do that, you can start modifying the xsd and apply it on your data.

Originally Posted by papusale

Thanks regress! This actually worked, and it also supports special characters!

One slight thing though;
at the very beginning of my XML schema it says: <?xml version="1.0"?>, and under it <unapproved xmlns:sql="urn:schemas-microsoft-com:xml-sql">
at the very beginning of excel XML file it says: <?xml version="1.0" encoding="UTF-8" standalone="true"?>

Is there a way to change it to exactly correspond to my XML schema?

Open the xml file in notepad and simply replace the lines (or even better, insert the lines in your xsd).

Originally Posted by papusale

Also regress,
The map.xsd has "NewsItemRow" and "NewsItemTable" instead of "Products".
When I tried to change them to "Products", it gave me an error while trying to load the map in Excel: "Cannot load the specified XML or schema source".

Download an XML editor and modify the name of NewsItemRow element in definition, meaning the change will be applied on the whole document (NewsItemTable can be amended similarly or as described above, in notepad, since those are only two lines).

Technically, you should be able to use Product and Producs in the xsd file, but it somehow doesn't work with the definition from http://bitwizards.com/blog/november-...rksheet-to-xml. I have an xsd at my workplace with which it works, but I don't have access to it right now. If you google other definitions, you will surely come across a solution. As I said above though, you can amend it in every XML editor as well.