Bring An XML File Into SQL 2005 1

Skittle · Nov 8, 2006

I have a big XML file I want to pull into SQL 2005.
I have a feeling I am missing somtheing obvious here but here goes...

In T-SQL how can I define the XML file and import it into a
declared variable that I can then shred using OPENXML or .NODES. Is the usual method to simply suck it in via DTS into a table field and then load it into a locally declared variable in a T-SQL process?

Come to think of it I guess spitting out data again afterwards to an XML file would be the same thing in reverse?

Dazed and confused
N+, MCDBA 2000, MCAD .NET

SQLDenis · Nov 8, 2006

Fastest way would be OPENROWSET(BULK

Code:

create table SomeTable (id int identity not null,
Doc XML not null);
GO

insert into SomeTable(Doc)
select * from OPENROWSET(BULK 'C:\SomeTable.xml',SINGLE_BLOB) as x

declare @doc varchar(max)
select @doc = convert(varchar(max),doc) from SomeTable

select @doc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

Skittle · Nov 10, 2006

Ok thanks chap - I shall have a fiddle with your example.

Dazed and confused

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Bring An XML File Into SQL 2005 1

Skittle

ISP

SQLDenis

Programmer

Skittle

ISP

Similar threads

Part and Inventory Search

Sponsor