Problem importing XML file with hyphens in element names

• parkesian

  SSC Veteran

  Points: 210

  More actions

  September 21, 2018 at 11:37 am

  #104442

  Hi,

  I am trying to use SQLXMLBulkLoad to load an xml file into SQL Server (the file to upload is greater than 2gb) and am having a problem as the source data has element names that contain hyphens, e.g. <first-name>.  I have managed to get a few sample records imported into the database excluding those fields, but as soon as I add in any of the elements with a hyphen in the name, the import fails with an error message (see attached screenshot) saying "Schema: Invalid Value for 'column'".  This only happens when there is a hyphen in an element name and I have no idea how to get around it.    The schema code is shown below:

  
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="jobs" sql:is-constant="1">
  <xsd:complexType>
  <xsd:sequence>
   <xsd:element name="job" sql:relation="mytemptable"
maxOccurs="unbounded">
    <xsd:complexType>
    <xsd:sequence>
     <xsd:element name="id" type="xsd:string" />
     <xsd:element name="title" type="xsd:string" />
     <xsd:element name="description" type="xsd:string" />
      <xsd:element name="town" type="xsd:string" />
      <xsd:element name="start-date" type="xsd:string" />
    </xsd:sequence>
    </xsd:complexType>
   </xsd:element>
  </xsd:sequence>
  </xsd:complexType>
</xsd:element>
</xsd:schema>


  I am unable to get the source data file amended to exclude the hyphens so I have to find a way to handle it in the import routine but after trying to resolve this for the last few days, I am still not able to get it to work.

  If anybody has any ideas on a way around this I would be very grateful.

  Thanks for reading