November 26, 2016 at 12:57 am
I need to read and convert a xml file into sql table
And I think I´m almost there although I get the content of the columns as NULLS
Can anyone help?
thanks
So far this is what I have:
USE OPENXMLTesting
GO
DROP TABLE XMLwithOpenXML
CREATE TABLE XMLwithOpenXML
(
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:\FCIV\BD10.xml', SINGLE_BLOB) AS x;
SELECT * FROM XMLwithOpenXML
SELECT CAST(xmldata as nvarchar(max))
FROM XMLwithOpenXML
DECLARE @XML AS XML, @hDoc AS INT, @sql NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT NAME, md5
FROM OPENXML(@hDoc, 'FCIV/FILE_ENTRY')
WITH
(
NAME [Char](150) '@NAME',
MD5 [Char](150) '@MD5'
)
This is the content of the xml file:
<FCIV>
<FILE_ENTRY>
<name>c:\fund_lib\7a70020000269.PDF</name>
<MD5>gDmxYLGDI+hq0irrVH9HfQ==</MD5>
</FILE_ENTRY>
<FILE_ENTRY>
<name>c:\fund_lib\77a0030000143.PDF</name>
<MD5>2zP9kkfV2JeGYmWvzodmHg==</MD5>
</FILE_ENTRY>
<FILE_ENTRY>
<name>c:\fund_lib\770030000234.PDF</name>
<MD5>Me9usoWFNMqtwVk07um68w==</MD5>
</FILE_ENTRY>
</FCIV>
November 26, 2016 at 2:21 am
Here is a quick example, should be enough to get you over this hurdle.
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.XMLwithOpenXML') IS NOT NULL DROP TABLE dbo.XMLwithOpenXML;
CREATE TABLE dbo.XMLwithOpenXML
(
Id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_XMLWITHOPENXML_ID PRIMARY KEY CLUSTERED
,XMLData XML NOT NULL
,LoadedDateTime DATETIME NOT NULL CONSTRAINT DFLT_DBO_XMLWITHOPENXML_LOADEDDATETIME DEFAULT (GETDATE())
);
INSERT INTO dbo.XMLwithOpenXML(XMLData)
VALUES ('<FCIV>
<FILE_ENTRY>
<name>c:\fund_lib\7a70020000269.PDF</name>
<MD5>gDmxYLGDI+hq0irrVH9HfQ==</MD5>
</FILE_ENTRY>
<FILE_ENTRY>
<name>c:\fund_lib\77a0030000143.PDF</name>
<MD5>2zP9kkfV2JeGYmWvzodmHg==</MD5>
</FILE_ENTRY>
<FILE_ENTRY>
<name>c:\fund_lib\770030000234.PDF</name>
<MD5>Me9usoWFNMqtwVk07um68w==</MD5>
</FILE_ENTRY>
</FCIV>');
SELECT
XOX.Id
,XOX.LoadedDateTime
,FILE_ENTRY.DATA.value('(name/text())[1]','NVARCHAR(256)') AS FNAME
,FILE_ENTRY.DATA.value('(MD5/text())[1]' ,'NVARCHAR(256)') AS FMD5
FROM dbo.XMLwithOpenXML XOX
OUTER APPLY XOX.XMLData.nodes('FCIV/FILE_ENTRY') FILE_ENTRY(DATA);
Output
Id LoadedDateTime FNAME FMD5
--- ----------------------- ------------------------------- -------------------------
1 2016-11-26 09:20:37.933 c:\fund_lib\7a70020000269.PDF gDmxYLGDI+hq0irrVH9HfQ==
1 2016-11-26 09:20:37.933 c:\fund_lib\77a0030000143.PDF 2zP9kkfV2JeGYmWvzodmHg==
1 2016-11-26 09:20:37.933 c:\fund_lib\770030000234.PDF Me9usoWFNMqtwVk07um68w==
November 26, 2016 at 2:27 am
Great!!!
many many thanks
November 26, 2016 at 2:55 am
Jay B (11/26/2016)
Great!!!many many thanks
You are very welcome.
😎
Just a quick note, do use the text() function when extracting element values, it is much more efficient than extracting the values without it.
The cost of this code
SELECT
XOX.Id
,XOX.LoadedDateTime
,FILE_ENTRY.DATA.value('(name/text())[1]','NVARCHAR(256)') AS FNAME
,FILE_ENTRY.DATA.value('(MD5/text())[1]' ,'NVARCHAR(256)') AS FMD5
FROM dbo.XMLwithOpenXML XOX
OUTER APPLY XOX.XMLData.nodes('FCIV/FILE_ENTRY') FILE_ENTRY(DATA);
is 1/3 of this one
SELECT
XOX.Id
,XOX.LoadedDateTime
,FILE_ENTRY.DATA.value('(name)[1]','NVARCHAR(256)') AS FNAME
,FILE_ENTRY.DATA.value('(MD5)[1]' ,'NVARCHAR(256)') AS FMD5
FROM dbo.XMLwithOpenXML XOX
OUTER APPLY XOX.XMLData.nodes('FCIV/FILE_ENTRY') FILE_ENTRY(DATA);
November 26, 2016 at 3:32 am
Understood. Thx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply