I'm trying to import an XML file into a SQL cell to process it. My first idea is do an OPENROWSET to keep the XML and the just divide it with NODES. One of the XML its too huge to keep it on a CELL, so the OPENROWSET cut the XML, so It's impossible to work with it then. That is the code:
DECLARE @XMLwithOpenXML TABLE
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO @XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn
,GETDATE()
FROM OPENROWSET(BULK 'C:\temp\PP015.xml', SINGLE_CLOB) AS x;
SELECT * FROM @XMLwithOpenXML
The second option is use the BCP to do the same, but I'm getting an error.
DECLARE @sql NVARCHAR(500)
SET @sql = 'bcp [ExternaDB].[dbo].[xmltab] IN "C:\temp\PP015.xml" -T -c'
EXEC xp_cmdshell @sql
select * from xmltab
I want to know if I'm on the correct way (How to work with an XML when is already in an SQL cell I know how to do it) and how I can BULK import the full XML into a cell without Length constraint.
CodePudding user response:
What is the size of the XML file on the file system?
Please try the following solution. It is very similar to yours with three differences:
SINGLE_BLOB
instead ofSINGLE_CLOB
- No need in
CONVERT(XML, BulkColumn)
DEFAULT
clause is used for the LoadedDateTime column
Additionally, you can use SSIS for the task. SSIS has a streaming XML Source Adapter with no XML file size limitation.
SQL
DECLARE @tbl TABLE(
ID INT IDENTITY PRIMARY KEY,
XmlData XML,
LoadedDateTime DATETIME DEFAULT (GETDATE())
);
INSERT INTO @tbl(XmlData)
SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\temp\PP015.xml', SINGLE_BLOB) AS x;
SELECT * FROM @tbl;
CodePudding user response:
Thanks for the help but I found the solution. SQL has configurate a maxium characters retrieved for XML data. To solve this issue just we have to reconfigure this parameter.