I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005: Easily importing XML Files

 

In SQL Server 2005 importing XML files became very easy.

OPENROWSET now supports the BULK keyword which lets us import XML files with ease.

A little example:

 

CREATE TABLE XmlImportTest
(
    xmlFileName VARCHAR(300),
    xml_data xml
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT  @xmlFileName = 'c:\TestXml.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData 
FROM
(
    SELECT  * 
    FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
SELECT * FROM XmlImportTest

DROP TABLE XmlImportTest

 

SINGLE_BLOB is recommended when importing XML files because only it supports all Windows encoding conversions.

kick it on DotNetKicks.com

Print | posted on Monday, June 18, 2007 8:47 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2005: Easily importing XML Files

One question dealing with PDF files. I would like to accomplish the same results as you have illustrated here; howerver, on my website I wish to display the imported PDF file as a link.
12/13/2007 8:24 PM | Samson
Gravatar

# re: SQL Server 2005: Easily importing XML Files

you can, you just have to save it to varbinary(max) type column
1/7/2008 3:33 PM | Mladen
Gravatar

# re: SQL Server 2005: Easily importing XML Files

hi, i am facing a problem passing a parameter to a stored procedure in sql 2005. I'm new to it and all the xml stuffs..
Basically i want to pass in a xml file into 3 separate tables depending on the fields i require. currently i did 3 stored procedures to load the xml file and passing the data into these 3 tables. I am able to do it but I would like to pass in a parameter which is the xml file path.

Anyone can help me? Thanks in advance.

This is my store procedure:

ALTER PROC [dbo].[insertAccount]
as
DECLARE @hDoc int
DECLARE @xml xml

set @xml=(select convert(xml,BulkColumn, 2)
From openrowset(Bulk'C:\TestXml.xml', single_blob) [rowsetresults])

exec sp_xml_preparedocument @hDoc OUTPUT, @xml
INSERT INTO CB_Account
SELECT *
FROM OPENXML(@hDoc, 'REQUEST/MESSAGE/ACCOUNT',2)
WITH (RUN_NO BIGINT '../HEADER/RUN_NO',
ACYC BIGINT 'ACYC',
.....
ACON INTEGER 'ACON')
exec sp_xml_removedocument @hDoc
7/2/2008 9:38 AM | Desmond
Gravatar

# re: SQL Server 2005: Easily importing XML Files

I have recently found out that there is a News Markup Language (NSML). I need to import that into an MSSQL 2005 table. The only problem I find is that this new standard uses syntax that my XML validators say is wrong.

More info on this standard here:
http://www.iptc.org/pages/index.php

Can anyone point me in the right direction as to how to import this without all the errors I get using the script on this topic??? I am not really skilled in XML.

Thanks everyone

Aecio
VLS Web
http://www.vlsweb.com.br
O primeiro provedor de hospedagem gerenciada do Brasil
8/14/2008 8:34 PM | Aecio Lemos
Gravatar

# re: SQL Server 2005: Easily importing XML Files

what if the file's not on the server?
8/30/2008 12:49 AM | Chris
Gravatar

# re: SQL Server 2005: Easily importing XML Files

How to get rid of truncate error in importing xml in SQL server
3/11/2009 7:01 AM | amy
Gravatar

# re: SQL Server 2005: Easily importing XML Files

This was very helpful to me. Thanks for submitting it.
5/20/2009 3:14 PM | Grady Christie
Gravatar

# re: SQL Server 2005: Easily importing XML Files

How can you get specific fields from the XML file and import them in separate fields of the table?

3/18/2010 2:33 AM | Camilo Niño
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET