BulkLoad Validation

I am new to XML and XML schemas and have been bashing my head against
a brick wall for too long with the following problem.
I want to use the BulkLoad method to read from an XML file based upon
a schema (XSD), and it should load the data to the SQL database
(SQLServer 2000).
I have included in the schema restrictions for certain fields, using
facets minlength, maxlength, length, to validate the legality of the
XML file during the BulkLoad process.
The BulkLoad works OK when the data is legal.
I want the BulkLoad to skip illegal records if a field has illegal
data.
The schema is as follows:
<?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"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:simpleType name="PANStr">
<xsd:restriction base="xsd:string">
<xsd:minLength value="13" />
<xsd:maxLength value="19" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="AVNStr">
<xsd:restriction base="xsd:string">
<xsd:minLength value="0" />
<xsd:maxLength value="4" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="ACStr">
<xsd:restriction base="xsd:string">
<xsd:length value="6" />
</xsd:restriction>
</xsd:simpleType>
<xsd:element name="Config">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="PAN" type="PANStr" />
<xsd:element name="AVN" type="AVNStr" />
<xsd:element name="AC" type="ACStr" />
</xsd:sequence>
</xsd:complexType>
<xsd:key name="ConfigKey1">
<xsd:selector xpath="." />
<xsd:field xpath="PAN" />
</xsd:key>
</xsd:element>
</xsd:schema>
An example of the XML file is as follows:
<?xml version="1.0"?>
<CardValues>
<Config>
<PAN>122112443423</PAN>
<AVN>1112</AVN>
<AC>111111</AC>
</Config>
<Config>
<PAN>235675674574576567</PAN>
<AVN>1115</AVN>
<AC>111111</AC>
</Config>
<Config>
<PAN>123456454563376578900</PAN>
<AVN>1125</AVN>
<AC>111111</AC>
</Config>
</CardValues>
The first record gets written to the SQL table even though the PAN
string length is smaller (12 characters) than the restriction in the
XSD (minlength=13).
The second record gets written to the SQL table (as expected).
The third record triggers the expected error: Type of column 'PAN' in
table 'Config' is too small to hold data
'12211777777777777777777777777777777777777'
I'm under the impression that the third record fails not due to the
maxlength restriction in the schema, but because the field in the
underlying SQL table has a length of 19. That is the reason that only
the maxlength restriction is working as expected.
Any advice would greatly be appreciated
Hi,
The SQLXMLBulkload component does not validate the input XML against the
supplied XSD. For example, facets like minlength and maxlength do not make
any difference to the Bulkload process. I hope this clarifies the issue.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: gosela@.netvision.net.il (Gidon Sela)
>Newsgroups: microsoft.public.sqlserver.xml
>Subject: BulkLoad Validation
>Date: 15 Apr 2004 11:48:31 -0700
>Organization: http://groups.google.com
>Lines: 89
>Message-ID: <b840dd4e.0404151048.7d0aeefe@.posting.google.com >
>NNTP-Posting-Host: 212.235.13.114
>Content-Type: text/plain; charset=ISO-8859-1
>Content-Transfer-Encoding: 8bit
>X-Trace: posting.google.com 1082054912 10201 127.0.0.1 (15 Apr 2004
18:48:32 GMT)
>X-Complaints-To: groups-abuse@.google.com
>NNTP-Posting-Date: Thu, 15 Apr 2004 18:48:32 +0000 (UTC)
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
e.de!news.glorb.com!postnews1.google.com!not-for-mail
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.xml:22246
>X-Tomcat-NG: microsoft.public.sqlserver.xml
>I am new to XML and XML schemas and have been bashing my head against
>a brick wall for too long with the following problem.
>I want to use the BulkLoad method to read from an XML file based upon
>a schema (XSD), and it should load the data to the SQL database
>(SQLServer 2000).
>I have included in the schema restrictions for certain fields, using
>facets minlength, maxlength, length, to validate the legality of the
>XML file during the BulkLoad process.
>The BulkLoad works OK when the data is legal.
>I want the BulkLoad to skip illegal records if a field has illegal
>data.
>The schema is as follows:
><?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"
> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
> <xsd:simpleType name="PANStr">
> <xsd:restriction base="xsd:string">
> <xsd:minLength value="13" />
> <xsd:maxLength value="19" />
> </xsd:restriction>
> </xsd:simpleType>
> <xsd:simpleType name="AVNStr">
> <xsd:restriction base="xsd:string">
> <xsd:minLength value="0" />
> <xsd:maxLength value="4" />
> </xsd:restriction>
> </xsd:simpleType>
> <xsd:simpleType name="ACStr">
> <xsd:restriction base="xsd:string">
> <xsd:length value="6" />
> </xsd:restriction>
> </xsd:simpleType>
> <xsd:element name="Config">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="PAN" type="PANStr" />
> <xsd:element name="AVN" type="AVNStr" />
> <xsd:element name="AC" type="ACStr" />
> </xsd:sequence>
> </xsd:complexType>
> <xsd:key name="ConfigKey1">
> <xsd:selector xpath="." />
> <xsd:field xpath="PAN" />
> </xsd:key>
> </xsd:element>
></xsd:schema>
>
>An example of the XML file is as follows:
><?xml version="1.0"?>
><CardValues>
> <Config>
> <PAN>122112443423</PAN>
> <AVN>1112</AVN>
> <AC>111111</AC>
> </Config>
> <Config>
> <PAN>235675674574576567</PAN>
> <AVN>1115</AVN>
> <AC>111111</AC>
> </Config>
> <Config>
> <PAN>123456454563376578900</PAN>
> <AVN>1125</AVN>
> <AC>111111</AC>
> </Config>
></CardValues>
>The first record gets written to the SQL table even though the PAN
>string length is smaller (12 characters) than the restriction in the
>XSD (minlength=13).
>The second record gets written to the SQL table (as expected).
>The third record triggers the expected error: Type of column 'PAN' in
>table 'Config' is too small to hold data
>'12211777777777777777777777777777777777777'
>I'm under the impression that the third record fails not due to the
>maxlength restriction in the schema, but because the field in the
>underlying SQL table has a length of 19. That is the reason that only
>the maxlength restriction is working as expected.
>Any advice would greatly be appreciated
>
|||Thanks for reply.
If I can't use BulkLoad to also verify the XML data before loading it
to SQL DB, what should I do to load and verify the XML data. The XML
file is very large, and speed is of an essence.
Thanks
Gidon
amarna@.online.microsoft.com ("Amar Nalla [MSFT]") wrote in message news:<027zS5JJEHA.4016@.cpmsftngxa10.phx.gbl>...[vbcol=seagreen]
> Hi,
> The SQLXMLBulkload component does not validate the input XML against the
> supplied XSD. For example, facets like minlength and maxlength do not make
> any difference to the Bulkload process. I hope this clarifies the issue.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> 18:48:32 GMT)
> cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
> e.de!news.glorb.com!postnews1.google.com!not-for-mail
|||Are you using native or managed code? The appropriate solution in the given
enviornment should be used.
Irwin
This posting is provided "AS IS" with no warranties, and confers no rights
"Gidon Sela" <gosela@.netvision.net.il> wrote in message
news:b840dd4e.0404190622.1704bb43@.posting.google.c om...[vbcol=seagreen]
> Thanks for reply.
> If I can't use BulkLoad to also verify the XML data before loading it
> to SQL DB, what should I do to load and verify the XML data. The XML
> file is very large, and speed is of an essence.
> Thanks
> Gidon
> amarna@.online.microsoft.com ("Amar Nalla [MSFT]") wrote in message
> news:<027zS5JJEHA.4016@.cpmsftngxa10.phx.gbl>...
|||I am using managed code (VB.NET)
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:<#nZC$GmJEHA.2464@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Are you using native or managed code? The appropriate solution in the given
> enviornment should be used.
> Irwin
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Gidon Sela" <gosela@.netvision.net.il> wrote in message
> news:b840dd4e.0404190622.1704bb43@.posting.google.c om...
> 18:48:32 GMT)
|||You'd have to use the XmlValidatingReader to first read through the data to
validate it. Then you could pass it to bulkload.
Check it out here:
http://msdn.microsoft.com/library/de...mberstopic.asp
Irwin
--
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gidon Sela" <gosela@.netvision.net.il> wrote in message
news:b840dd4e.0404210212.3e187104@.posting.google.c om...[vbcol=seagreen]
>I am using managed code (VB.NET)
> "Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message
> news:<#nZC$GmJEHA.2464@.TK2MSFTNGP10.phx.gbl>...