|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Bulk load and XSD problem...with. Basically my feed wants to insert NULLS into a column that will not allow them. I've tried adding a DEFAULT value, but for some reason it's not working. I think the problem is coming from the fact that the element in the XML feed is sometimes completely empty (self closed), whilst other times it contains data that I want added to the database. Would this cause an issue? Here's my schema... (I've put !!! next to the two lines with problems -- commenting these lines out fixes everything. As does setting the database column to ALLOW NULL. Unfortunately these two solutions are NOT options! :( ) Can anyone tell me why it's not working? Thanks! - Johnny <?xml version="1.0" encoding="UTF-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas- microsoft-com:mapping-schema"> <!-- definition of simple elements --> <xs:element name="pId" type="xs:string" sql:field="SKU"/> <xs:element name="name" type="xs:string" sql:field="ProductName"/> <xs:element name="desc" type="xs:string" sql:field="Description"/> <xs:element name="awCatId" type="xs:string" sql:field="CategoryInput1" /> <xs:element name="awCat" type="xs:string" sql:field="CategoryInput2" default="empty" /> !!! <xs:element name="mCat" type="xs:string" sql:field="CategoryInput3"/> <xs:element name="brand" type="xs:string" sql:field="Brand" default="no brand" /> <xs:element name="mImage" type="xs:string" sql:field="ImageURL"/> <xs:element name="awLink" type="xs:string" sql:field="ProductURL"/> <xs:element name="display" type="xs:string" sql:field="Price"/> <!-- definition of attributes --> <!-- definition of complex elements --> <xs:element name="products" sql:is-constant="1"> <xs:complexType> <xs:sequence> <xs:element name="merch" sql:is-constant="1"> <xs:complexType> <xs:sequence> <xs:element name="prod" sql:relation="ProductsTable"> <xs:complexType> <xs:sequence> <xs:element ref="pId"/> <xs:element ref="name"/> <xs:element ref="desc"/> <xs:element name="cat" sql:is-constant="1"> <xs:complexType> <xs:sequence> <xs:element ref="awCatId"/> <xs:element ref="awCat"/> !!! <xs:element ref="mCat"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element ref="brand"/> <xs:element ref="mImage"/> <xs:element ref="awLink"/> <xs:element name="price" sql:is-constant="1"> <xs:complexType> <xs:sequence> <xs:element ref="display" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema> I had a look at something like this recently and got it to work by setting
the SQLXMLBulkload object's Transaction property to true. Have a look at this and see if it helps: http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/bfa31c49-6ae5-4a5d-bcde-cd520e0cdf70/ HTH wBob Show quoteHide quote "JohnnyW" wrote: > Hi there, I've come across an annoying issue which I hope someone can help me > with. Basically my feed wants to insert NULLS into a column that will not > allow them. I've tried adding a DEFAULT value, but for some reason it's not > working. > > I think the problem is coming from the fact that the element in the XML feed > is sometimes completely empty (self closed), whilst other times it contains > data that I want added to the database. Would this cause an issue? > > Here's my schema... (I've put !!! next to the two lines with problems -- > commenting these lines out fixes everything. As does setting the database > column to ALLOW NULL. Unfortunately these two solutions are NOT options! :( ) > > Can anyone tell me why it's not working? > > Thanks! > - Johnny > > <?xml version="1.0" encoding="UTF-8"?> > <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas- > microsoft-com:mapping-schema"> > <!-- definition of simple elements --> > <xs:element name="pId" type="xs:string" sql:field="SKU"/> > <xs:element name="name" type="xs:string" sql:field="ProductName"/> > <xs:element name="desc" type="xs:string" sql:field="Description"/> > <xs:element name="awCatId" type="xs:string" sql:field="CategoryInput1" /> > <xs:element name="awCat" type="xs:string" sql:field="CategoryInput2" > default="empty" /> !!! > <xs:element name="mCat" type="xs:string" sql:field="CategoryInput3"/> > <xs:element name="brand" type="xs:string" sql:field="Brand" default="no > brand" /> > <xs:element name="mImage" type="xs:string" sql:field="ImageURL"/> > <xs:element name="awLink" type="xs:string" sql:field="ProductURL"/> > <xs:element name="display" type="xs:string" sql:field="Price"/> > <!-- definition of attributes --> > > <!-- definition of complex elements --> > <xs:element name="products" sql:is-constant="1"> > <xs:complexType> > <xs:sequence> > <xs:element name="merch" sql:is-constant="1"> > <xs:complexType> > <xs:sequence> > <xs:element name="prod" sql:relation="ProductsTable"> > <xs:complexType> > <xs:sequence> > <xs:element ref="pId"/> > <xs:element ref="name"/> > <xs:element ref="desc"/> > <xs:element name="cat" sql:is-constant="1"> > <xs:complexType> > <xs:sequence> > <xs:element ref="awCatId"/> > <xs:element ref="awCat"/> !!! > <xs:element ref="mCat"/> > </xs:sequence> > </xs:complexType> > </xs:element> > <xs:element ref="brand"/> > <xs:element ref="mImage"/> > <xs:element ref="awLink"/> > <xs:element name="price" sql:is-constant="1"> > <xs:complexType> > <xs:sequence> > <xs:element ref="display" /> > </xs:sequence> > </xs:complexType> > </xs:element> > </xs:sequence> > </xs:complexType> > </xs:element> > </xs:sequence> > </xs:complexType> > </xs:element> > </xs:sequence> > </xs:complexType> > </xs:element> > </xs:schema> > > -- > Message posted via http://www.sqlmonster.com > > Hi Bob, thanks for your comment. I checked out that link and sadly I get a
permissions error if I set Transaction to TRUE. Not sure why. In my problem the problem only seems to occur when the element is self- closing, I think. For example: <name>Bob</name> <name/> <name>Johnny</name> It just doesn't seem to want to use the DEFAULT to over-write an empty element... So weird! :( Bob wrote: >I had a look at something like this recently and got it to work by setting >the SQLXMLBulkload object's Transaction property to true. > >Have a look at this and see if it helps: >http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/bfa31c49-6ae5-4a5d-bcde-cd520e0cdf70/ > >HTH >wBob > -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-xml/200904/1 Permissions problem? That doesn't sound right.
How are you running your bulk load? Is it from VBScript? Or .net? How are you editing this file? What specific error to you get when you try and run the file once you have set the Transaction option to true? Show quoteHide quote "JohnnyW via SQLMonster.com" wrote: > Hi Bob, thanks for your comment. I checked out that link and sadly I get a > permissions error if I set Transaction to TRUE. Not sure why. > > In my problem the problem only seems to occur when the element is self- > closing, I think. > > For example: > <name>Bob</name> > <name/> > <name>Johnny</name> > > It just doesn't seem to want to use the DEFAULT to over-write an empty > element... So weird! :( > > > Bob wrote: > >I had a look at something like this recently and got it to work by setting > >the SQLXMLBulkload object's Transaction property to true. > > > >Have a look at this and see if it helps: > >http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/bfa31c49-6ae5-4a5d-bcde-cd520e0cdf70/ > > > >HTH > >wBob > > > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-xml/200904/1 > > Thanks for your reply.
I'm using a little custom-made app to test the Bulk Load with before we upload to the live site. It's written in C#/.Net, I think. The exact error is something like "You do not have permission to use the bulk load statement". Turning Transaction mode off allows it work, though. But, I'm not completely sure why Transaction mode would fix this problem, though? I mean, what is Transaction mode?? :) Thanks, - Johnny Damien wrote: >Permissions problem? That doesn't sound right. > >How are you running your bulk load? Is it from VBScript? Or .net? How are >you editing this file? > >What specific error to you get when you try and run the file once you have >set the Transaction option to true? > >> Hi Bob, thanks for your comment. I checked out that link and sadly I get a >> permissions error if I set Transaction to TRUE. Not sure why. >[quoted text clipped - 18 lines] >> >HTH >> >wBob -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-xml/200904/1 OK, try using a bit of VBScript to run the bulk load, as per the example I
posted a link to, see if you can get that to work. Transaction mode is a property of SQLXMLBulkLoad which makes the load run as a single transaction. It also seems to observe defaults, where non-transaction mode does not, as per the link I posted above. Show quoteHide quote "JohnnyW via SQLMonster.com" wrote: > Thanks for your reply. > > I'm using a little custom-made app to test the Bulk Load with before we > upload to the live site. It's written in C#/.Net, I think. > > The exact error is something like "You do not have permission to use the bulk > load statement". Turning Transaction mode off allows it work, though. > > But, I'm not completely sure why Transaction mode would fix this problem, > though? I mean, what is Transaction mode?? :) > > Thanks, > - Johnny > > > Damien wrote: > >Permissions problem? That doesn't sound right. > > > >How are you running your bulk load? Is it from VBScript? Or .net? How are > >you editing this file? > > > >What specific error to you get when you try and run the file once you have > >set the Transaction option to true? > > > >> Hi Bob, thanks for your comment. I checked out that link and sadly I get a > >> permissions error if I set Transaction to TRUE. Not sure why. > >[quoted text clipped - 18 lines] > >> >HTH > >> >wBob > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-xml/200904/1 > > I'm not sure of the solution but the problem might be that <name /> means
that the name contains an empty string which is different from being NULL. NULL data is shown by omitting the element altogether or using an attribute of xsi:nil set to true. So maybe the format of the XML needs to be different to reflect this. "JohnnyW via SQLMonster.com" <u50889@uwe> wrote in message news:948cb043020c8@uwe...Show quoteHide quote > Hi Bob, thanks for your comment. I checked out that link and sadly I get a > permissions error if I set Transaction to TRUE. Not sure why. > > In my problem the problem only seems to occur when the element is self- > closing, I think. > > For example: > <name>Bob</name> > <name/> > <name>Johnny</name> > > It just doesn't seem to want to use the DEFAULT to over-write an empty > element... So weird! :( > > > Bob wrote: >>I had a look at something like this recently and got it to work by setting >>the SQLXMLBulkload object's Transaction property to true. >> >>Have a look at this and see if it helps: >>http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/bfa31c49-6ae5-4a5d-bcde-cd520e0cdf70/ >> >>HTH >>wBob >> > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-xml/200904/1 >  
Other interesting topics
XML EXPLICIT Help
OPENXML to accept both attributes and elements Import XML with multiple sublevel nodes XML parsing: ... illegal xml character missing elements while passing XML from .NET to SQL 2005 XQuery - Only return if not null Retrieving XML Field data as Text Need help w/ importing data via web services dealing with special characters using SP_XML_PREPAREDOCUMENT How to Find Missing > in an XML Doc |
|||||||||||||||||||||||
