sql database integrity: bulk

Showing posts with label bulk. Show all posts

Tuesday, March 27, 2012

Creating xsd schema file for a xml file ...

Hi ...
I need to bulk insert the xml data below. I am working on some issues related to the Itentity column (you may have read the other question).
The xml below uses elements with attributes. Is the schema file formated correctly to extract the attributes from the SITE element and then also extract the attributes from the ATMOSPHERIC element ... ? Not sure about the schema file ?
Thanks,
Chris

////////////////////////////////////////////////////
// schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="atmospheric" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime"/>
<xsd:attribute name="airtemp" type="xsd:string" sql:field="Temperature"/>
<xsd:attribute name="dewpoint" type="xsd:string" sql:field="DewPoint"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

////////////////////////////////////////////////////
// xml data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0" name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM" airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11" windspeedgust="19" winddirectionavg="265" winddirectiongust="295" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="1900" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="450" waterlevel="0">
<traffic datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82" volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-60" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>

////////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestRWISRawAtmospheric] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_DataSourceId] DEFAULT ('OH'),
[ProductInstanceId] [char] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_ProductInstanceId] DEFAULT ('5abbbc86-fb2c-4703-9589-b55f763ee150'),
[SystemId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RpuId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SensorId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObsDateTime] [datetime] NULL ,
[InsertDateTime] [datetime] NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_InsertDateTime] DEFAULT (getdate()),
[Temperature] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DewPoint] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelativeHumidity] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPD] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPDGust] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMin] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRAvg] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMax] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipIntensity] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipType] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipRate] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pressure] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Visability] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMax] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMin] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WetBulbTemp] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastPrecipStart] [datetime] NULL ,
[LastPrecipEnd] [datetime] NULL ,
[PrecipAccum1Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum3Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum6Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum12Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum24Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_MacgowanTestRWISRawAtmospheric] PRIMARY KEY NONCLUSTERED
(
[RecordId]
) WITH FILLFACTOR = 70 ON [PRIMARY]
) ON [PRIMARY]
GO

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...

Sorry about the confusion. I was having some issues with bulk

loading using the IDENTITY Column in the target table. I was

setting the COM attribute KeepIdentity to TRUE rather than FALSE.

The schema and xml are correct.

Thanks,

Chris

Creating xsd schema file for a xml file ...

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...
Sorry about the confusion. I was having some issues with bulk loading using the IDENTITY Column in the target table. I was setting the COM attribute KeepIdentity to TRUE rather than FALSE.
The schema and xml are correct.
Thanks,
Chris

Friday, February 17, 2012

Creating FilePath for a BULK INSERT SP

I am creating an SP that will bulk insert data, the person calling the SP just needs to provide the file path and a number for the file and then the idea is i append this together to get the file to insert.

I have the SQL below which will not go past the BULK INSERT it is to do with my FilePath can anyone explain how I should be typing this?

Thanks very much

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[Get_DataFromFiles]

(

@.FileNumber VarCHAR(255),

@.FileID INT

)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- 1. Insert Tasks Table Data and obtain the new ID

BULK INSERT Tasks FROM @.FilePath + 'FileNumber' + @.FileID WITH (FIELDTERMINATOR = '<$%^&>')

declare @.sql varchar(8000)
select @.sql = 'BULK INSERT Tasks FROM ' + @.FilePath + 'FileNumber' + @.FileID + ' WITH (FIELDTERMINATOR = ''<$%^&>'')'

exec (@.sql)|||

No but I forgot to show the backslash in the code i posted should read

select @.sql = 'BULK INSERT Tasks FROM ' + @.FilePath + '\FileNumber' + @.FileID + ' WITH (FIELDTERMINATOR = ''<$%^&>'')'

I still get an error though which is

Incorrect syntax near '@.FilePath'

|||Thanks for your help didnt realise I had to build the statement then execute it rather than just build as needed. Much appreciated.

Tuesday, March 27, 2012

Creating xsd schema file for a xml file ...

Creating xsd schema file for a xml file ...

Friday, February 17, 2012

Creating FilePath for a BULK INSERT SP

sql database integrity

Blog Archive

About Me