Help With Parsing XML File into SQL Server With Hyphens in Element Names

[ianparkes]

Hi,

I am fairly new to working with XML files and schemas etc. I have got a file which we receive daily that needs to be imported into an SQL Server database. I have set up an xsd file to import the data and it works fine for most of the elements, however, there are several elements that have a hyphen in the element name. Whenever I try to run the script to import the file it generates an error code 80004005 "schema: invalid value for column" and this only happens when I add any of the elements with a hyphen in the name.

The source file is around 2gb and it's not possible to get the elements changed so I need to find a way to handle the hyphens during the import. I considered trying to do a find and replace on the field names in the raw file before importing but it is so big that it is not really practical to do this as it takes forever to load and navigate.

If anybody has any ideas on how I can get around it I would be really grateful as I've spent a couple of days looking at this and really can't find a way around it and there doesn't seem to be anything similar when I have tried googling the problem. If anybody needs and further information or screenshots etc then please let me know.

Thanks for reading

 

[k5tm]

The simplest approach would be XSLT. Here is a short tutorial.

Then, you need to find the appropriate transform example on which to base your transform. Renaming elements is rather simple. Here is an example.

Be sure to come back here to ask questions...

Tom Morrison
Consultant

 

[k5tm]

Actually, this is probably a better example:

https://social.msdn.microsoft.com/F...1e7e996/rename-node-xslt-10?forum=xmlandnetfx

Tom Morrison
Consultant

 

[KNKL]

but have you've tried declaring the character as an entity?

you would in your case put this under the xml header:

<!ENTITY - "&#45;">