Link to home
Start Free TrialLog in
Avatar of EddieIT
EddieIT

asked on

XML/XSD file, setting field type

Hello Experts, I'm Using SQL 2008 R2 Visual Studio/DTS services to import from XML files. I came across a field which contains a special character "&", this field is mapped in the XSD as string, but at the time of import the import fails because of the special character. How should I code the XSD so it can accept special characters?
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="PART_INB_IFD">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="PART_SEG">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="SEGNAM" type="xs:string" />
              <xs:element minOccurs="0" name="TRNTYP" type="xs:string" />
              <xs:element minOccurs="0" name="PRTNUM" type="xs:unsignedShort" />
              <xs:element minOccurs="0" name="ACTCODE" type="xs:unsignedShort" />
              <xs:element minOccurs="0" name="TYPCOD" type="xs:string" />
              <xs:element minOccurs="0" name="COMCOD" />
              <xs:element minOccurs="0" name="LNGDESC" type="xs:string" />
              <xs:element minOccurs="0" name="UPCCOD" />
              <xs:element minOccurs="0" name="EANCOD" type="xs:unsignedLong" />
              <xs:element minOccurs="0" name="UNTPAK" type="xs:unsignedByte" />
              <xs:element minOccurs="0" name="UNTCAS" />
              <xs:element minOccurs="0" name="UNTWGT" type="xs:decimal" />
              <xs:element minOccurs="0" name="UNTLEN" />
              <xs:element minOccurs="0" name="UNTWID" />
              <xs:element minOccurs="0" name="UNTHGT" />
              <xs:element minOccurs="0" name="CASLEN" />
              <xs:element minOccurs="0" name="CASWID" />
              <xs:element minOccurs="0" name="CASHGT" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
Avatar of HooKooDooKu
HooKooDooKu

The '&' character is a special XML character just like '<' and '>'.  If the content of your XML includes the text '&' you are supposed to replace it with an 'entity'.

While you can create your own entities, XML has the following 5 entities predefined for you:

&lt; the less than sign <
&gt; the greater than sign >
&amp; the ampersand &
&apos; the singe quote (apostrophe) '
&quot; the double quote "
I'm not sure whether & is a valid character in table/column naming convention.
Designation of the field type is not the issue.
Forgot to include an example:

If you had an element by the name FLD&NAM, your XML would need to be encoded like this:
<xs:element minOccurs="0" name="FLD&amp;NAM" type="xs:string" />

Every XML reader is supposed to take the text "FLD&amp;NAM" and convert it to "FLD&NAM" as it reads it.
Avatar of EddieIT

ASKER

Thank you for your comments, but the issue is not a field name, but a field value. This is the field which has an issue:
<xs:element minOccurs="0" name="LNGDESC" type="xs:string" />

and this is the value which fails:

TEST123 & TEST5678
Are you sure the error deals with the value of the field and not the length?

Could you post the error message you get when trying to process the XML file?
Avatar of EddieIT

ASKER

The error is not too clear, it just points me to the field, and the only difference I see is the character "&" . What is the restriction for the string in XML?
I'm confused because there is no '&' anywhere in your sample text.

Is this '&' inside the XML file?  If so, then it still doesn't matter and the &amp; entity is still needed to represent the & in the string:

<xmltag>TEST123 &amp; TEST5678</xmltag>

yields a 'xmltag' with a #PCDATA value of "TEST123 & TEST5678".
Avatar of EddieIT

ASKER

Here is the sample text from XML file:

  <SALIN1>Ernst & Young</SALIN1>

Here is the XSD file line for this field:

<xs:element minOccurs="0" name="SALIN1" type="xs:string" />

I'm not sure why my import into SQL table fails as my SQL table field is VARCHAR50 and the XSD field is defined as string
ASKER CERTIFIED SOLUTION
Avatar of HooKooDooKu
HooKooDooKu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial