Importing XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Importing XML

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-01-08 : 12:52:20

We recieve about a hundred reports in xml format XML files per day and I want to import them into a table to allow easy access. I can open the xml file and extract the needed data,( ClientNo, ReportType, ReportDate ), but what I an struggling with is being able to being it all together to inset into the table ( below ) . I know i need to use bulkinsert, but can never get it to work

Decalre @LedgerKey UniqueIdentifier
Declare @reportType varchar(50)
Declare @CleintNumber varchar(6)
Declare @ReportDate Smalldatetime
Declare @FullReportPath varchar(250)

CREATE TABLE [dbo].[tbReports](
[LedgerKey] [uniqueidentifier] NOT NULL,
[ReportType] [varchar](50) NOT NULL,
[ClientNo] [varchar](6) NOT NULL,
[ReportDate] [smalldatetime] NOT NULL,
[ReportFile] [xml] NOT NULL,
CONSTRAINT [PK_tbReports] PRIMARY KEY CLUSTERED
(
[LedgerKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

thanks in advance

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 12:54:32

Follow this..

http://support.microsoft.com/kb/316005

Poor planning on your part does not constitute an emergency on my part.

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-01-08 : 12:58:45

quote:
Originally posted by dataguru1971

Follow this..

http://support.microsoft.com/kb/316005

Poor planning on your part does not constitute an emergency on my part.

I dont want to import the data into fields, I want to import the xml file into a SQL field

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 13:13:32

Not sure how an article titled "How to import XML into SQL Server with the XML Bulk Load component" doesn't lead you to a useable answer, considering you are asking how to use Bulk insert to load XML data into SQL tables.

This link has samples.
http://msdn.microsoft.com/en-us/library/ms171806.aspx

Poor planning on your part does not constitute an emergency on my part.

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-01-08 : 13:21:40

Im sorry, I dont think I have explained myself correctly. I have grabbed this snipp which should show you waht I am trying to do

CREATE TABLE T (XmlColumn xml NOT NULL)

insert into T select * from openrowset(bulk 'D:\databasexmlfiles\oracle_schema.xml',single_clob) as XmlColumn

What I want to do is the same but with the table i specified earlier

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 14:02:08

I see, I would extract the 4 key fields you need and insert them into the resulting tbale first, then update THAT ROW to SET the xml report file data using

UPDATE tblReport
SET ReportFile = (select * from openrowset(bulk 'D:\databasexmlfiles\oracle_schema.xml',single_clob) as XmlColumn)
WHERE LedgerKey = @LedgerKey ....etc

You haven't quite given the whole procedure so I can't really offer a full solution...but I get what you want to do now.

You could also have a table with a 1 column Key, and 2nd xml Column containing the full file, and update the reference table with your ReportDate etc info with the Key column from the table containing the full file.

How are you extracting the 4 columns you need to identify the report?

Poor planning on your part does not constitute an emergency on my part.

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-01-08 : 14:08:14

Hi thanks for you help so far,

previously to importing this file, I would have already opened it, taken certin information from the file and done a lookup on other tables. That part has been easy to do, It's the insert thats causing the problem. If possible , i want to hold this information in the one table.

I have come up with this insert statement.

INSERT INTO dbo.tbReports(LedgerKey, ReportType, ClientNo, ReportDate, ReportFile)
VALUES ( @LedgerKey , @reportType, @ClientNumber, @ReportDate
,(SELECT * FROM OPENROWSET(BULK 'C:\temp\217784.xml' , SINGLE_BLOB) AS x))

That works fine, however I need to beable to use a variable instead of the 'C:\temp\217784.xml' . I need to replace it with @FullReportPath

Any Ideas?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 14:26:41

Ahh...I see.

So if you have the variable @FullReportPath populated you would use dynamic SQL like below. Do a PRINT @SQL first to verify syntax, copy the results of the print and execute in Query analyzer to confirm results work properly.


DECLARE @SQL varchar(max)

Select @SQL = 
'INSERT INTO dbo.tbReports(LedgerKey, ReportType, ClientNo, ReportDate, ReportFile)
VALUES (' +  @LedgerKey + ',' + @reportType + ',' + @ClientNumber + ',' + @ReportDate + '
,(SELECT * FROM OPENROWSET(BULK ' + @FullReportPath + ' , SINGLE_BLOB) AS x))'

exec sp_ExecuteSQL @SQL

Poor planning on your part does not constitute an emergency on my part.

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-01-08 : 14:34:25

looks cool .. though unfortunatly it's throwing up and error

The data types varchar and uniqueidentifier are incompatible in the add operator

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 14:42:23

It's not actually an ADD operator. It is a string concatonation operator. Declare your variables as varchar OR CAST the numeric /datetime variables as varchar.
something like below

DECLARE @SQL varchar(max)

Select @SQL =
'INSERT INTO dbo.tbReports(LedgerKey, ReportType, ClientNo, ReportDate, ReportFile)
VALUES (' + Cast(@LedgerKey as varchar(10)) + ',' + @reportType + ',' + @ClientNumber + ',' + convert(varchar(12),@ReportDate,101) + '
,(SELECT * FROM OPENROWSET(BULK ' + @FullReportPath + ' , SINGLE_BLOB) AS x))'

exec sp_ExecuteSQL @SQL

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources