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="unqu alified" elementFormDefault="qualif ied" 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>
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqu
<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>
I'm not sure whether & is a valid character in table/column naming convention.
Designation of the field type is not the issue.
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&NAM" type="xs:string" />
Every XML reader is supposed to take the text "FLD&NAM" and convert it to "FLD&NAM" as it reads it.
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&NAM" type="xs:string" />
Every XML reader is supposed to take the text "FLD&NAM" and convert it to "FLD&NAM" as it reads it.
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
<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?
Could you post the error message you get when trying to process the XML file?
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 & entity is still needed to represent the & in the string:
<xmltag>TEST123 & TEST5678</xmltag>
yields a 'xmltag' with a #PCDATA value of "TEST123 & TEST5678".
Is this '&' inside the XML file? If so, then it still doesn't matter and the & entity is still needed to represent the & in the string:
<xmltag>TEST123 & TEST5678</xmltag>
yields a 'xmltag' with a #PCDATA value of "TEST123 & TEST5678".
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
<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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
While you can create your own entities, XML has the following 5 entities predefined for you:
< the less than sign <
> the greater than sign >
& the ampersand &
' the singe quote (apostrophe) '
" the double quote "