XML File insert into a table field

Question

XML File insert into a table field

jharvey6 76850

Old Hand

Points: 381
More actions
August 31, 2015 at 11:49 am

#302691

I have been tasksed to create a data table and stored procedure to extract a special formatted XML file that is an attachment with a standard XML envelope. The XML file is an attchment in a node within the XML wrapper. There are other MIME files (pdf's ) that are handle by a seperate procedure. But I need to just extract the XML file attached along with those and put it into the datable with some other PK?FK fields.
Is a blob the best datatype. How to I insert that XML file into it?
LOST!!

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

jharvey6 76850 (8/31/2015)
I have been tasksed to create a data table and stored procedure to extract a special formatted XML file that is an attachment with a standard XML envelope. The XML file is an attchment in a node within the XML wrapper. There are other MIME files (pdf's ) that are handle by a seperate procedure. But I need to just extract the XML file attached along with those and put it into the datable with some other PK?FK fields.
Is a blob the best datatype. How to I insert that XML file into it?
LOST!!

First impression is that this is no too complex but we need more details in order to provide a proper answer. Can you please elaborate on the details?

😎

jharvey6 76850 Old Hand Points: 381 More actions · Answer 2

Context What a concept. Apologies should have been more succinct. After scrubbing a lot of data I have this XML wrapper coming to me.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"

xmlns:sch="http://somewhere.us.com/is/cs/schema" xmlns:sch1="http://somewhere.us.com/is/common/schema">

<soapenv:Header />

<soapenv:Body>

<sch:ANBRRequest>

<sch:ANBRReq>

<sch1:userId>XXYYZZ</sch1:userId>

<sch1:clientNumber>0001</sch1:clientNumber>

<sch:SaveFormsList>

<sch:SaveForms>

<sch:accountKey>

</sch:accountKey>

<sch1:repId>UUU</sch1:repId>

<sch1:formId>YYYY</sch1:formId>

<sch:docDate>

<sch1:day>09</sch1:day>

<sch1:month>01</sch1:month>

<sch1:year>2015</sch1:year>

</sch:docDate>

<sch:docId>XXXXXXYYYYYYYZZZZZZ</sch:docId>

<sch1:updateADTS />

<sch:attachmentRawData contentType="application/pdf">

<sch:attachmentData></sch:attachmentData>

[Attachments would typically be here. Show in attached file]

</sch:attachmentRawData>

</sch:SaveForms>

</sch:SaveFormsList>

<sch:fullNoNStdXML contentType="application/xml">

[Attachment would typically be here.To Be inserted to a data column rowset. Index created by DocDate]

</sch:fullNoNStdXML>

</sch:ANBRReq>

</sch:ANBRRequest>

</soapenv:Body>

</soapenv:Envelope>

So from this the application pdf's node is handle by another procedure for document storage. I need to store the fullNoNStdXML attachment to a datatable that would also include some of the data elements in the body for indexing (i.e. docId, month, year, day, userid, etc..)

GG