Load XML File into Table from T-SQL (With no CLR)

Is there a better way to do this in SQL Server 2005. I do this in SQL Server
2000 with VBScript and SQLXMLBULKLOAD.SQLXMLBulkLoad, so doing it in C# with
CLR is a slam dunk, but is it now possible with just a couple of T-SQL
commands to load XML type with a file?

Can this be done:

DECLARE @xmlCountries xml

SET @xmlCountries = SOMTHING('c:\Countries.xml')



-- THIS WORKS, BUT IS THERE A BETTER WAY?

Countries.xml

<?xml version="1.0" encoding="utf-8" ?>
<Countries>
<Country>
<Name>United States</Name>
<ISOCode>US</ISOCode>
<Language>English</Language>
</Country>
<Country>
<Name>Costa Rica</Name>
<ISOCode>CR</ISOCode>
<Language>Spanish</Language>
</Country>
<Country>
<Name>Belize</Name>
<ISOCode>BZ</ISOCode>
<Language>English</Language>
</Country>
</Countries>


USE Testing
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'dbo.t_Countries') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.t_Countries
GO

CREATE TABLE dbo.t_Countries (
ctry_pk int IDENTITY (1, 1) NOT NULL ,
ctry_Name varchar(255) DEFAULT '' NOT NULL ,
ctry_ISO_Code char(2) DEFAULT ' ' NOT NULL ,
ctry_Language varchar(255) DEFAULT '' NOT NULL
)
GO

SET NOCOUNT ON

CREATE TABLE #t_Temp_Table (temp_pk int IDENTITY (1, 1) NOT NULL,
temp_Line_Data nvarchar(max) DEFAULT '' NOT NULL)

-- SQL Server 2005 does not have this option on by default.

--EXEC sp_configure 'xp_cmdshell', 1
--GO
--RECONFIGURE
--GO

INSERT #t_Temp_Table EXEC master.dbo.xp_cmdshell 'TYPE c:\Countries.xml'


DECLARE @vcmXML varchar(max)
DECLARE @intDocumentHandle int

SELECT @vcmXML = CASE temp_pk WHEN 1 THEN ISNULL(RTRIML(temp_Line_Data), '')
ELSE @vcmXML + ISNULL(RTRIM(temp_Line_Data), '') END
FROM #t_Temp_Table
ORDER BY temp_pk ASC

DROP TABLE #t_Temp_Table

EXEC sp_xml_preparedocument @intDocumentHandle OUTPUT, @vcmXML

INSERT INTO dbo.t_Countries
SELECT Name, ISOCode, Language
FROM OPENXML(@intDocumentHandle, '/Countries/Country', 2)
WITH (Name varchar(255),
ISOCode char(2),
Language varchar(255))

EXEC sp_xml_removedocument @intDocumentHandle
GO

SELECT *
FROM dbo.t_Countries
GO

SET NOCOUNT OFF
.

Relevant Pages

  • Multiple Left Joins and an Inner Join
    ... This is an Access 2000 database that is linked via ODBC to ... are linked tables brought in from SQL Server. ... >or may not have a State or Country. ... >query that will pull Contact information as well as ...
    (microsoft.public.access.queries)
  • Re: question about conversion
    ... Dates (even in SQL Server) are always stored *internally* the same way. ... problem is that as soon as a date is written down each country have its own ... (YYYYMMAA that is always read correctly regardless of regional settings) ... Sql Server (date is saved in dd/mm/yyy format in datetime field). ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: transforming the export
    ... processed the COUNTRY attribute. ... Allan Mitchell (Microsoft SQL Server MVP) ... I support PASS - the definitive, global community ... for SQL Server professionals - http://www.sqlpass.org ...
    (microsoft.public.sqlserver.dts)
  • Re: multi count
    ... If you want one country ... countfrom rounds where country = 'USA' ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ... > there's a bit more to it when building dynamic queries,> but that'll set you on a path... ...
    (microsoft.public.sqlserver.programming)
Loading