I have received an Excel file (verion 2010) and based on the requirement I was asked to save it as an XML file and then import into SQL data ready to be queried in normal SELECT statements.
Below is the list of steps I followed but now that I have a SELECT on the data, no records is returned. I'm not sure whether it's due to the conversion that I did to get an XML file from an Excel file, or there is a problem in my
- To obtain a
XMLfile from the
.xlsxfile, I opend the file in EXCEL 2010 and saved it in 'XML spread sheet 2003 (*.XML)'.
I imported the XML file in a table using the code below:
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime) SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() FROM OPENROWSET(BULK 'C:\DEV\TestXML.xml', SINGLE_BLOB) AS x; SELECT * 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 SNo,Salutation,PatientNRIC,FirstName,LastName,Gender,Race FROM OPENXML(@hDoc, 'ROOT/SNo/Salutation/PatientNRIC/FirstName/LastName/Gender/Race') WITH ( SNo [nvarchar](50) '@SNo', Salutation [nvarchar](100) '@Salutation', PatientNRIC [nvarchar](255) '@PatientNRIC', FirstName [nvarchar](255) '@FirstName', LastName [nvarchar](255) '@LastName', Gender [nvarchar](255) '@Gender', Race [nvarchar](255) '@Race' )
EXEC sp_xml_removedocument @hDoc GO
However what I get is only the name of columns, the actual data has not been returned:
I am totally new to OPENXML comment and working with XML files, tried reading different posts but still couldn't figure out why no data is shown in my select query. Any help is appreciated.