Forgot your password?
Document Actions
How to Import Blobs To SQL Server (2005 and Later)
This tip describes how to import blobs into SQL Server 2005 and Later using TSQL. In past versions, there is a command line tool to provide this feature called textcopy.exe. In SQL Server 2005 and later versions it's possible to do it using only TSQL. In this tip, I'll use an XML Document on the file system
First, create and XML Document in any folder that SQL Server Service Account have read permissions. I'll put the XML file (xmldoc.xml) in G:\XML folder (this can change depends of the enviroment). The contents of XML Document looks like the structure below
<documents>
<document type="letter" id="001" date="2009-01-26"/>
<document type="memo" id="003" date="2009-01-27"/>
<document type="travel request" id="005" date="2009-01-28"/>
<document type="memo" id="006" date="2009-01-29"/>
<document type="memo" id="009" date="2009-01-25"/>
<document type="letter" id="004" date="2009-01-31"/>
</documents>
The next step is use the OPENROWSET command to retrieve the Blob (it's not necessary to allow the use of OPENROWSET to perform this).
SELECT CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK N'G:\XML\XmlDoc.xml', SINGLE_BLOB)
AS Arquivo
This can retrieve the XML Document and converts the contents to XML DataType. You can shredding the XML using the XML methods of course.
-- Capture the contents into XML Variable
DECLARE @XML XML
SET @XML = (
SELECT CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK N'G:\XML\XmlDoc.xml', SINGLE_BLOB)
AS Arquivo)
-- Shredding the XML
SELECT
Docs.Doc.value('@type','nvarchar(20)') as [type],
Docs.Doc.value('@id','int') as [Id],
Docs.Doc.value('@date','nvarchar(20)') as [Date]
FROM @XML.nodes('/documents/document') As Docs(Doc)
Hacker Rangers - Gamification for CyberSecurity