October 16, 2009 at 4:24 pm
I am getting a new xml file from a vendor that I need to upload to a table. I think I have set up correctly ot run out of a DTS package and it runs successfully, just the data loaded into the table is all nulls.
Here is the table
[font="Courier New"]CREATE TABLE [dbo].[impXMLReport](
[listingid] [varchar](20) NULL,
[errormessage] [varchar](100) NULL,
[totalcount] [varchar](50) NULL,
[vendorurl] [varchar](100) NULL,
[searchimpressions] [varchar](10) NULL,
[detailedpageviews] [varchar](10) NULL,
[clickdirectedtraffic] [varchar](10) NULL,
[contacts] [varchar](10) NULL
) [/font]
This is my file reportschema.xml
[font="Courier New"]<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="listingid" sql:relation="impXMLReport" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="listingid" sql:field="listingid" type="xsd:string" sql:use-cdata="1" />
<xsd:element name="errormessage" sql:field="errormessage" type="xsd:string" sql:use-cdata="1" />
<xsd:element name="count" sql:field="totalcount" type="xsd:string" sql:use-cdata="1" />
<xsd:element name="vendorurl" sql:field="vendorurl" type="xsd:string" sql:use-cdata="1" />
<xsd:element name="searchimpressions" sql:field="searchimpressions" type="xsd:string" sql:use-cdata="1" />
<xsd:element name="detailedpageviews" sql:field="detailedpageviews" type="xsd:string" sql:use-cdata="1" />
<xsd:element name="clickdirectedtraffic" sql:field="clickdirectedtraffic" type="xsd:string" sql:use-cdata="1" />
<xsd:element name="contacts" sql:field="contacts" type="xsd:string" sql:use-cdata="1" />
</xsd:sequence>
<xsd:attribute name="listingid" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>[/font]
This is my Active X script for upload
[font="Courier New"]'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
'Option Explicit
Function Main()
Dim objXBulkLoad
Set objXBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
objXBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=WEBSERVER5;integrated security=SSPI;DATABASE=ReportDatabase;"
'Optional Settings
objXBulkLoad.KeepIdentity = False
objXBulkLoad.KeepNulls = True
objXBulkLoad.CheckConstraints = False
objXBulkLoad.SchemaGen = True
objXBulkLoad.ErrorLogFile = "\\WEBSERVER5\xmlrpt\reports\log\dataerror.log"
'Executing the bulk-load
objXBulkLoad.Execute "\\WEBSERVER5\xmlrpt\reportschema.xml", "\\WEBSERVER5\xmlrpt\reports\BrokerID.XML"
Main = DTSTaskExecResult_Success
End Function[/font]
A sample of the BrokerID.XML file
[font="Courier New"]<listings>
<listing>
<listingdata>
<listingid>3844861</listingid>
<errormessage code="6">No Matching Property Found</errormessage>
<activephotos>
<count>5</count>
</activephotos> <vendorurl>http://www.vendor.com/homedetails/2136173914_pid</vendorurl>
</listingdata>
<performance>
<searchimpressions>322</searchimpressions>
<detailedpageviews>1</detailedpageviews>
<clickdirectedtraffic>0</clickdirectedtraffic>
<contacts>0</contacts>
</performance>
</listing>
</listings>[/font]
Any help is appreciated
October 21, 2009 at 4:12 am
I'm not sure I understand the problem. Are you saying you want to load nulls but can't or that the data doesn't appear to be loading at all?
October 21, 2009 at 1:30 pm
I am sorry for any confusion. When I load the xml file using the Active X script above it creates (x) number of records in the database table but all the values for every record and column in the table are all NULL
October 21, 2009 at 2:06 pm
I'm not familiar with doing this in ActiveX scripts, but in T-SQL's XQuery, I'm used to seeing nulls if I've got a mismatch in the column definition, or if I haven't included the schema definition correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 22, 2009 at 3:48 am
I run into the same problem with SSIS when I have a schema definition mismatch. I'd advise checking that first. Make sure to check not only structure but (if applicable) datatypes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply