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

DECLARE @c xml
SELECT @c = BulkColumn FROM OPENROWSET(BULK 'c:\temp\countries.xml', SINGLE_BLOB) AS A
INSERT INTO t_Countries SELECT T.C.value('Name[1]', 'VARCHAR(255)'),
T.C.value('ISOCode[1]', 'VARCHAR(2)'),
T.C.value('Language[1]', 'VARCHAR(255)')
FROM @c.nodes('Countries/Country') AS T(C)

Dan

I knew there was easy answer, thank you very much.

"anonymous" wrote: