Importing XML from file to XML field

Question

Post reply

Importing XML from file to XML field

GibsonM111

SSC Rookie

Points: 35
More actions
September 3, 2008 at 9:06 am

#99255

How can I import XML from a file into an XML field in a SQL Server 2005 table?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

Try something like this...

DECLARE @FileName VARCHAR(150)

SET @FileName = 'C:\file.xml'

CREATE TABLE #XmlImport

(

xmlFileName VARCHAR(300),

xml_data xml

)

-- dynamic sql is just so we can use @FileName variable in OPENROWSET

EXEC ( '

INSERT INTO #XmlImport(xmlFileName, xml_data)

SELECT ''' + @FileName + ''', xmlData

FROM

(SELECT *

FROM OPENROWSET (BULK ''' + @FileName + ''' , SINGLE_BLOB) AS XMLDATA

)AS FileImport (XMLDATA)

' )

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 2

I didn't mean to wink I just meant to close the brackets!!!!

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

GibsonM111 SSC Rookie Points: 35 More actions · Answer 3

Thanks for the reply. I've been trying this approach, but it seems like each way I try, it says the xml file does no exist when I have the exact path.

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 4

Can you post your code, and details of the error message?

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

GibsonM111 SSC Rookie Points: 35 More actions · Answer 5

In order to make sure I wasn't making a typo I pretty much copied and pasted your code:

DECLARE @FileName VARCHAR(500)

SET @FileName = 'C:\parcels_poly_center.xml'

CREATE TABLE #XmlImport

(

xmlFileName VARCHAR(500),

xml_data xml

)

-- dynamic sql is just so we can use @FileName variable in OPENROWSET

EXEC ( '

INSERT INTO #XmlImport(xmlFileName, xml_data)

SELECT ''' + @FileName + ''', xmlData

FROM

(SELECT *

FROM OPENROWSET (BULK ''' + @FileName + ''' , SINGLE_BLOB) AS XMLDATA

)AS FileImport (XMLDATA)

' )

And the error is:

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "C:\parcels_poly_center.xml" does not exist.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

I don't know what account OPENROWSET from SQL Server runs under, but you should make sure that it has the necessary access to that directory and file.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GibsonM111 SSC Rookie Points: 35 More actions · Answer 7

If I'm logged into SQL Server Management Studio using Windows Authentication, shouldn't SQL Server use my account?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

GibsonM111 (9/3/2008)
If I'm logged into SQL Server Management Studio using Windows Authentication, shouldn't SQL Server use my account?

Not necessarily. The actual security context that SQL Server uses for external facilities is different for every facility. I do not know what OPENROWSET to SQLNCLI uses, but most of them do not use your Windows security context.

If it is like Linked Servers, then it might be trying to impersonate your Windows context, but it might be using something else entirely.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GibsonM111 SSC Rookie Points: 35 More actions · Answer 9

This seemed to have worked:

EXEC('

INSERT INTO mapData_center(sourceXML)

SELECT *

FROM OPENROWSET(BULK ''C:\parcels_poly_center.xml'', SINGLE_CLOB) AS X'

)

Thanks for all the help.