Apologies in advance if I am asking something really obvious but I have an XML file that is provided daily, from which I need to extract the data and import it into a table in SQL Server. I think the issue I am having is down to namespaces but I don't fully understand how to work with them and despite reading numerous posts, I still cannot get any of the suggested solutions to work with my data so was hoping that somebody may be able to point me in the right direction please?
OK, the data comes from the following file (I have truncated it to a couple of records for ease of reading):
<jobs xmlns="https://adview.online/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://adview.online/XMLSchema jobs.xsd ">
<desc>Parts Analyst Job Description</desc>
<town>West Midlands West Midlands (County) Birmingham</town>
<desc>Data Processor Job Desc</desc>
<town>North West Cheshire Chester</town>
<company>Test Company 2</company>
The code I am using below works if I remove the xmlns="https://adview.online/XMLSchema" text from the <jobs> tag at the start of the XML file, and successfully returns the data for each job, however with the xmlns code in place, I get no records returned (I have simplified the code to just do a select statement rather than an insert/update one for testing purposes): There are 2 lines that I have commented out on the sp_xml_preparedocument line and the openxml line as I was using these to try to get the namespaces to work but they just return all null values rather than the actual values in the fields (so I think I was getting warmer but not quite there).
DROP TABLE xmlimport
CREATE TABLE XMLImport
Id INT IDENTITY PRIMARY KEY,
INSERT INTO XMLImport(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'E:\imports\testfile1.xml', SINGLE_BLOB) AS x;
SELECT * FROM XMLImport
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLImport
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
--EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, N'<root xmlns:n="https://adview.online/XMLSchema" />'
SELECT title, company
FROM OPENXML(@hDoc, '/jobs/job', 2)
--FROM OPENXML(@hDoc, '/n:jobs/n:job', 2)
[title] varchar(170) 'title',
[company] varchar(100) 'company'
EXEC sp_xml_removedocument @hDoc
As I said, I'm really not that familiar with XML (which unfortunately will probably be quite obvious having read this message!!) so any advice would be very much appreciated as I am running out of ideas and really not sure what else to try.