Reading XML data from SQL Table

Question

Reading XML data from SQL Table

contact.lash

SSC Veteran

Points: 234
More actions
October 24, 2017 at 11:34 pm

#402616

Hi Experts,
I have loaded an XML file which is 750MB in size, its created as a BLOB. Following is an output of the XML files and I do not know how to read this into SQL table. The main confusion is how to read the nodes. I have used SSIS to read data but character limit is set to 255 which is an issue for us.
Appreciate your earliest response.
Thank you.
Regards
Las

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

contact.lash - Tuesday, October 24, 2017 11:34 PM
Hi Experts,
I have loaded an XML file which is 750MB in size, its created as a BLOB. Following is an output of the XML files and I do not know how to read this into SQL table. The main confusion is how to read the nodes. I have used SSIS to read data but character limit is set to 255 which is an issue for us.
Appreciate your earliest response.
Thank you.
Regards
Las

Quick thought, you can use bulk import into a table and parse it from there if the file is less than 2Gb
😎

In the passed I've had to deal with files which were tens of Gb in size, it's all doable but which method to use depends on the circumstances.

contact.lash SSC Veteran Points: 234 More actions · Answer 2

Hi Eirikur,
Thank you for your response.

I was able to load the file into SQL, the issue is how can i read the structure nodes? as you can see in the image #text node contains all the information.
For ex: InstallationPoint has 5+ attribute nodes. i.e ID, MaintenancePlanningPlantID, OperatingPlantID etc.

I have attached the first part of the XML file just to show the format,as i couldn't upload the entire file.

Any advise would be appreciate.
Thank you.
Regards,

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Eirikur Eiriksson

SSC Guru

Points: 182915

October 26, 2017 at 12:18 am

#1965273

Can you pos here a sample of the XML please?
😎

sturner SSC-Insane Points: 22411 More actions · Answer 4

Basically you can use OPENXML to represent the XML data as a relational table. You need to know what flavor of XML you are dealing with though and pass the appropriate params to it. As an example, below is an example of how I parse error codes out of the XML returned by the FED check image gateway:
   declare @SomeXML varchar(8000)
   declare @DocHandle int
   declare @xml xml
   declare @retCode int
   declare @reasonCode int
   declare @message varchar(200)

   select @SomeXML = XMLErrMsg from ChkImageResponse where ID = @ID
   EXEC sp_xml_preparedocument @DocHandle OUTPUT, @ErrMsg
-- Get the pertinent Return Codes and message into local variables
SELECT @retCode = returnCode, @reasonCode = reasonCode, @message = messages
FROM OPENXML (@DocHandle, '/responsePackage/response',2) WITH (returnCode int, reasonCode int, messages varchar(200))
EXEC sp_xml_removedocument @DocHandle
  select RetCode=@retCode , ReasonCode=@reasonCode , Message=@message

And ... of course you can use XQuery if the data is in an XML datatype. :^O

The probability of survival is inversely proportional to the angle of arrival.