Creating an XML file with a specific schema

I would need some help with creating an XML file which has the schema (or structure) as in the attached image.

I would like to generate the XML file from the Excel file that I have attached.

It would be greatly appreciated if someone can instruct me with step-by-step explanation as to how to create the XML with the given schema.

Much obliged!

Originally Posted by Naveed Raza

Hi Papusale

u have some blank value in Product_ID_Required column

Hi Naveed,

Yes this is true. However for any given title (column), content will not be mandatory, meaning that blanks are allowed.
PS: Ignore the names given in the XML example. Even if it says "Product_ID_Required", it's really not required to have any content.

Originally Posted by Naveed Raza

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

Yes Naveed, that is correct .

Thank you very much for your help Naveed. Much appreciated!

Hi Naveed (or anyone else who may help),

Don't mean to bother, but can I kindly ask for an update on this request?
I would have to have my project done by Monday, so I'm quite under pressure.

Really appreciate your help! Thank you again

Originally Posted by Naveed Raza

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

It works! Simply amazing Naveed! I greatly appreciate your help!

So I will simply copy and paste the Module1 code to any other new excel file I want to create an XML file with?
Also, will it automatically generate other data missing from this example excel file (that are listed in the first XML schema example), such as "Product_Condition_Required", "Language_Optional" etc?
Because some other excel files will have some data that is missing from the one I attached.

PS: The original XML example file has "<unapproved xmlns:sql="urn:schemas-microsoft-com:xml-sql">" at the top, but missing from the module you created. Will this make any kind of difference in terms of functionality?

Originally Posted by Naveed Raza

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 Naveed,

I added a reputation for you.
However there's one thing missing until I change the status to SOLVED.
It's what I asked in my previous post:

"Also, will it automatically generate other data missing from this example excel file (that are listed in the first XML schema example), such as "Product_Condition_Required", "Language_Optional" etc?
Because some other excel files will have some data that is missing from the one I attached."

Originally Posted by Naveed Raza

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 ....

Hi Naveed,

I have tried exactly what you have instructed with a new list, but unfortunately it will not include any new data types (I added "Product_Condition_Required" and "Language_Optional"). However it does generate the content for the rest of the data types (Title_Required etc.).

Originally Posted by Naveed Raza

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

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

Simply amazing!
Everything is solved!

There's just one thing Naveed, and I apologize for this, but it's important; the special characters are actually required, so leaving out ä, ö, å etc. is out of the question for my project.
Do you have any suggestions?
After this, the whole thing will be SOLVED!

Originally Posted by regresss

tl;dr: have you tried xml add-in? Your xml schema has a very simple structure, so I would suggest use this add-in.

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?

Originally Posted by Naveed Raza

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

Hello Naveed,

Yes this newer version works with blank content as well, but this is not a problem, because I can always replace blank cells with "-" or "None".

The problem is the special characters. As I mentioned, since some websites have the letter "&" in their URL, and your module currently does not support that letter, it will not be possible to create an XML based on those websites.

I will require all special characters (such as ä,ö,å etc.). It is very important, otherwise the whole module will be obsolete.

In the XML schema example file, you can see that there are actually special characters inside the brackets (both & and ä), so it should be possible.

Originally Posted by regresss

Sure. Fill



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.

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?

Originally Posted by regresss

Sure. Fill



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.

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".

Originally Posted by regresss

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




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.

Sure, I can modify with an XML editor, however it would have been convenient if the xsd had everything set based on my schema. As I said, I have tried to replace the words in the xsd file with notepad and in excel I get an error and can't open it.

Originally Posted by regresss

It is slightly inconvenient, but nothing serious. With an XML editor, it's just a matter of replacing 4 lines and changing 1 attribute, a matter of 10 seconds.

Attach the xsd file and I will look at it now. I've just installed the add-in on my desktop.

Yes it's true. However if there's a way to have the xsd file fully correspond to the XML schema, I would be more than delighted .
Thank you very much regresss for your help. Much appreciate it! .