Re: annotation issue with SQLXML - BulkLoad

To Mr. Lifesaver Bob,

Yes, I made it work with your xsd schema. But you successfully ignored my
questions, so that I can understand why there are such problems.
You underestimate the number of changes, but there are at least 7-8
small-to-major differences between the xsd file you have posted and the one
I had.
I compared everything in two columns of excel sheet, line by line ....yes
there are tons of differences. Everthing looks simple with this one small
file, but with so many nested xsd's I may be suicidal if I start modifying
files.
Have no idea what tool you used, "why" things are so screwy with XML
validity vs. flat files.
Still have no idea "WHY WHY WHY WHY" these vendors are spitting out xsd
files that are so useless and helpless with Microsoft's tools.
Simple example: you replaced the first two "integer" with "decimal", and
left the third one as "integer"
This is extremely weird.

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="INGREDIENT_SUBSTANCES">
<xs:complexType>
<xs:sequence>
<xs:element name="ING" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="ISID" type="xs:integer"
minOccurs="1" maxOccurs="1" />
<xs:element name="ISIDDT" type="xs:date"
minOccurs="0" maxOccurs="1" />
<xs:element name="ISIDPREV" type="xs:integer"
minOccurs="0" maxOccurs="1" />
<xs:element name="INVALID" type="xs:integer"
minOccurs="0" maxOccurs="1" />
<xs:element name="NM" type="xs:string"
minOccurs="1" maxOccurs="1" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Lilya


+++++++++++++++++++++++++++++++++++
"Travis McGee" <travisGatesMcGee@hotmail.com> wrote in message news:...
> Thanks Bob,
>
> I will experiment with this....but there are so many files to
> modify.....nearly impossible; and I am sure there will be lots of other
> glitches.
>
> What is the main source of the problem????!!!
> Standards issue? Version issue? Microsoft issue?
>
> Still does not understand, when SQLXML Bulk Load creates the table, how
> will it decide about the length of the <xsd:element name="ISIDDT"
> type="xsd:string" /> for field. Usually the values will be truncated if
> it picks SQL Server default value, without looking at the entire xml data
> file. What am I missing here?
>
> Best Regards
> Lilya
>
>
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:1934263D-E61D-40C3-8A1E-9D3FD32CE1FB@microsoft.com...
>>I got this revised schema1.xsd to work:
>>
>> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
>> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>> <xsd:element name="INGREDIENT_SUBSTANCES" sql:is-constant="1" >
>> <xsd:complexType>
>> <xsd:sequence>
>> <xsd:element name="ING" sql:relation="ING" maxOccurs="unbounded">
>> <xsd:complexType>
>> <xsd:sequence>
>> <xsd:element name="ISID" type="xsd:decimal" />
>> <xsd:element name="ISIDDT" type="xsd:string" />
>> <xsd:element name="ISIDPREV" type="xsd:decimal" />
>> <xsd:element name="INVALID" type="xsd:integer" />
>> <xsd:element name="NM" type="xsd:string" />
>> </xsd:sequence>
>> </xsd:complexType>
>> </xsd:element>
>> </xsd:sequence>
>> </xsd:complexType>
>> </xsd:element>
>> </xsd:schema>
>>
>> "Travis McGee" wrote:
>>
>>> "Travis McGee" <travisGatesMcGee@hotmail.com> wrote in message
>>> news:eLS4%23jYAJHA.1228@TK2MSFTNGP02.phx.gbl...
>>> >I am getting "relationship expected on" errors on SQLXML Loading Code
>>> >when
>>> >I
>>> > am creating the tables and then perhaps adding.inserting the data.
>>> >
>>> > Set oLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
>>> > oLoad.ConnectionString = gsConn
>>> > oLoad.ErrorLogFile = "c:\error.log"
>>> > oLoad.SchemaGen = True
>>> > oLoad.SGDropTables = False
>>> > oLoad.BulkLoad = False
>>> > oLoad.Execute "c:\schema1.xsd" , "c:\data1.xml"
>>> >
>>> > These files came from outsitde- many of them; for the purpose of bulk
>>> > inserting into the database.
>>> > What is the problem? Missing Annotation? Is SQLXML too picky?
>>> > Why do we have to touch all of the Schema files? Are there two
>>> > different
>>> > standards?
>>> >
>>> > These are small files
>>> > http://www.HumanGenome.org/data/schema1.xsd
>>> > http://www.HumanGenome.org/data/data1.xml
>>> > is giving me....relationship expected on 'ING'
>>> >
>>> > and then
>>> > http://www.HumanGenome.org/data/schema2.xsd
>>> > http://www.HumanGenome.org/data/data2.xml
>>> > is giving me....relationship expected on 'COMBINATION_PACK_IND'
>>> >
>>> > This has something to do with missing annotation, I think.
>>> >
>>> > Can somebody help me
>>> >
>>> > Lilya Lopekha
>>> >
>>>
>>>
>>>
>
>

As far as I can tell, the only differences required to make this work are:
1) add sql:is-constant with namespace
2) convert xs:integers to xs:decimals or even xs:string
3) convert xs:date to xs:string

I haven't been ignoring your questions, I'm afraid I can't answer questions
as to why xsds won't work with SQL Server. Maybe they are meant to be
compatible with .net. Have you tried any .net methods of reading XML files
with schemas? Maybe there are compatibility problems with XML data-types and
SQL Server data-types. It's not unusual in ETL to have to load to a staging
table (eg where all columns are VARCHAR) and then clean the data.

All I know is, these xsd are not quite suitable for SQL Server straightaway,
but need a few minor modifications.

I have been working on way to make this easy, so it can be used against
multiple xsds. Try this:

IF OBJECT_ID( 'dbo.usp_fix_xsd' ) IS NOT NULL
DROP PROC dbo.usp_fix_xsd
GO

------------------------------------------------------------------------------------------------
-- Fix up an .xsd file for import into SQL Server START
-- Example call:
-- EXEC dbo.usp_fix_xsd 'C:\...\Genome\original_schema1.xml'
------------------------------------------------------------------------------------------------

CREATE PROC dbo.usp_fix_xsd

@filepath NVARCHAR(MAX), -- the filepath of the .xsd you want to fix
@debug_yn BIT = 0

AS

SET NOCOUNT ON

-- Load up orginal schema
DECLARE @xml XML
DECLARE @sql NVARCHAR(MAX)
DECLARE @loop_limit INT

-- Avoid infinite loops
SET @loop_limit = 99

SET @sql = 'SELECT @xml = x.y
FROM OPENROWSET( BULK ''' + @filepath + ''', SINGLE_CLOB ) x(y)'

IF @debug_yn = 1
SELECT @sql

-- Extract loaded XML
EXEC sp_executesql @sql, N'@xml XML OUT', @xml OUT


-- Inspect the XML beforehand
IF @debug_yn = 1
SELECT 'Before' AS source,
x.y.value('local-name(.)', 'VARCHAR(50)'),
x.y.value( '@type', 'SYSNAME' )
FROM @xml.nodes( '//xs:element' ) x(y)


-- Add sql:is-constant attribute
SET @xml.modify( 'declare namespace
sql="urn:schemas-microsoft-com:mapping-schema";
insert attribute sql:is-constant{1} as last into (//xs:element)[1]' )

-- Update all @type attributes with value xs:integer to xs:decimal
DECLARE @i INT SET @i = 0
WHILE @xml.exist( '(//xs:element[@type = "xs:integer"]/@type)[1]' ) = 1
BEGIN
SET @xml.modify( 'replace value of (//xs:element[@type =
"xs:integer"]/@type)[1] with "xs:decimal"' )
SET @i = @i + 1
IF @i > @loop_limit
BEGIN
RAISERROR( 'Too many loops.', 16, 1 )
BREAK
END
END

IF @debug_yn = 1
IF @i > 0
PRINT CAST( @i AS VARCHAR(10) ) + ' xs:integer converted to xs:decimal'

SET @i = 0

-- Update all @type attributes with value xs:date to xs:string
WHILE @xml.exist( '(//xs:element[@type = "xs:date"]/@type)[1]' ) = 1
BEGIN
SET @xml.modify( 'replace value of (//xs:element[@type =
"xs:date"]/@type)[1] with "xs:string"' )
SET @i = @i + 1
IF @i > @loop_limit
BEGIN
RAISERROR( 'Too many loops.', 16, 1 )
BREAK
END
END

IF @debug_yn = 1
IF @i > 0
PRINT CAST( @i AS VARCHAR(10) ) + ' xs:date converted to xs:string'

IF @debug_yn = 1
-- Inspect the XML afterwards
SELECT 'After' AS source,
x.y.value('local-name(.)', 'VARCHAR(50)'),
x.y.value( '@type', 'SYSNAME' )
FROM @xml.nodes( '//xs:element' ) x(y)

-- Return the modified schema
SELECT @xml
GO

All I did, was start with a working bulk load example and .xsd and go from
there. My other advice to you is to remain calm!

My final advice to you is try the MSDN SQL XML newsgroup which is much more
busy than this one:
http://forums.microsoft.com/msdn/Sho...ID=89&SiteID=1

Present simple problems with sample data, desired results etc and keep
trying!
Oh and remain calm, again ; )

(PS I know how frustrating SQLXMLBulkLoad can be. I love it when it doesn't
do anything, doesn't tell you it hasn't done anything and won't tell you why).

How did you get on with this stored procedure? Did it work?

I noticed it wasn't quite doing the namespace correctly but don't know if
that caused a problem for you.

"Travis McGee" wrote:

> To Mr. Lifesaver Bob,
>
> Yes, I made it work with your xsd schema. But you successfully ignored my
> questions, so that I can understand why there are such problems.
> You underestimate the number of changes, but there are at least 7-8
> small-to-major differences between the xsd file you have posted and the one
> I had.
> I compared everything in two columns of excel sheet, line by line ....yes
> there are tons of differences. Everthing looks simple with this one small
> file, but with so many nested xsd's I may be suicidal if I start modifying
> files.
> Have no idea what tool you used, "why" things are so screwy with XML
> validity vs. flat files.
> Still have no idea "WHY WHY WHY WHY" these vendors are spitting out xsd
> files that are so useless and helpless with Microsoft's tools.
> Simple example: you replaced the first two "integer" with "decimal", and
> left the third one as "integer"
> This is extremely weird.
>
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
>
> <xs:element name="INGREDIENT_SUBSTANCES">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ING" minOccurs="1" maxOccurs="unbounded">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ISID" type="xs:integer"
> minOccurs="1" maxOccurs="1" />
> <xs:element name="ISIDDT" type="xs:date"
> minOccurs="0" maxOccurs="1" />
> <xs:element name="ISIDPREV" type="xs:integer"
> minOccurs="0" maxOccurs="1" />
> <xs:element name="INVALID" type="xs:integer"
> minOccurs="0" maxOccurs="1" />
> <xs:element name="NM" type="xs:string"
> minOccurs="1" maxOccurs="1" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
>
> Lilya
>
>
> +++++++++++++++++++++++++++++++++++
> "Travis McGee" <travisGatesMcGee@hotmail.com> wrote in message news:...
> > Thanks Bob,
> >
> > I will experiment with this....but there are so many files to
> > modify.....nearly impossible; and I am sure there will be lots of other
> > glitches.
> >
> > What is the main source of the problem????!!!
> > Standards issue? Version issue? Microsoft issue?
> >
> > Still does not understand, when SQLXML Bulk Load creates the table, how
> > will it decide about the length of the <xsd:element name="ISIDDT"
> > type="xsd:string" /> for field. Usually the values will be truncated if
> > it picks SQL Server default value, without looking at the entire xml data
> > file. What am I missing here?
> >
> > Best Regards
> > Lilya
> >
> >
> > "Bob" <Bob@discussions.microsoft.com> wrote in message
> > news:1934263D-E61D-40C3-8A1E-9D3FD32CE1FB@microsoft.com...
> >>I got this revised schema1.xsd to work:
> >>
> >> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> >> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> >> <xsd:element name="INGREDIENT_SUBSTANCES" sql:is-constant="1" >
> >> <xsd:complexType>
> >> <xsd:sequence>
> >> <xsd:element name="ING" sql:relation="ING" maxOccurs="unbounded">
> >> <xsd:complexType>
> >> <xsd:sequence>
> >> <xsd:element name="ISID" type="xsd:decimal" />
> >> <xsd:element name="ISIDDT" type="xsd:string" />
> >> <xsd:element name="ISIDPREV" type="xsd:decimal" />
> >> <xsd:element name="INVALID" type="xsd:integer" />
> >> <xsd:element name="NM" type="xsd:string" />
> >> </xsd:sequence>
> >> </xsd:complexType>
> >> </xsd:element>
> >> </xsd:sequence>
> >> </xsd:complexType>
> >> </xsd:element>
> >> </xsd:schema>
> >>
> >> "Travis McGee" wrote:
> >>
> >>> "Travis McGee" <travisGatesMcGee@hotmail.com> wrote in message
> >>> news:eLS4%23jYAJHA.1228@TK2MSFTNGP02.phx.gbl...
> >>> >I am getting "relationship expected on" errors on SQLXML Loading Code
> >>> >when
> >>> >I
> >>> > am creating the tables and then perhaps adding.inserting the data.
> >>> >
> >>> > Set oLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
> >>> > oLoad.ConnectionString = gsConn
> >>> > oLoad.ErrorLogFile = "c:\error.log"
> >>> > oLoad.SchemaGen = True
> >>> > oLoad.SGDropTables = False
> >>> > oLoad.BulkLoad = False
> >>> > oLoad.Execute "c:\schema1.xsd" , "c:\data1.xml"
> >>> >
> >>> > These files came from outsitde- many of them; for the purpose of bulk
> >>> > inserting into the database.
> >>> > What is the problem? Missing Annotation? Is SQLXML too picky?
> >>> > Why do we have to touch all of the Schema files? Are there two
> >>> > different
> >>> > standards?
> >>> >
> >>> > These are small files
> >>> > http://www.HumanGenome.org/data/schema1.xsd
> >>> > http://www.HumanGenome.org/data/data1.xml
> >>> > is giving me....relationship expected on 'ING'
> >>> >
> >>> > and then
> >>> > http://www.HumanGenome.org/data/schema2.xsd
> >>> > http://www.HumanGenome.org/data/data2.xml
> >>> > is giving me....relationship expected on 'COMBINATION_PACK_IND'
> >>> >
> >>> > This has something to do with missing annotation, I think.
> >>> >
> >>> > Can somebody help me
> >>> >
> >>> > Lilya Lopekha
> >>> >
> >>>
> >>>
> >>>
> >
> >
>
>
>