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>
EddieITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HooKooDooKuCommented:
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 "
0
arnoldCommented:
I'm not sure whether & is a valid character in table/column naming convention.
Designation of the field type is not the issue.
0
HooKooDooKuCommented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

EddieITAuthor Commented:
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
0
arnoldCommented:
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?
0
EddieITAuthor Commented:
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?
0
HooKooDooKuCommented:
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".
0
EddieITAuthor Commented:
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
0
HooKooDooKuCommented:
If the text in you XML file is "<SALIN1>Ernst & Young</SALIN1>", then there is your problem.

In an xml file, the ampersand ( & ) denotes the start of an entity.  To prevent the ampersand from being decoded incorrectly, change '&' in the xml file to '&amp;'.

You would get a similar problem if the text of your xml file looked like this:
<SALIN1>Ernst < Young</SALIN1>
You would throw the xml parser into chaos because it would think the '<' in the middle of your text was the start of a new node.  So if you really wanted a less-than-sign in your xml text, you would replace it with the entity for a less-than-sign:
<SALIN1>Ernst &lt; Young</SALIN1>

Again, every xml parser that reads '&amp;' will convert it to a '&' as well as '&lt;' in to '<'.


And if you're getting the xml file that contains <SALIN1>Ernst & Young</SALIN1> from someone else, then you MUST go back to them and tell them they are creating an invalid xml file... as in they are violating the specifications for xml (not simply causing you a headache).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.