XML file to DB

Hi.

Please sincerely give me hints about which build-in services can be used to migrate
the content of XML file to DB table?

(I suppose some XML related build-in services will be needed.)

Best regards,
Shogo Tatsuzawa

We just found the tips below.
http://stackoverflow.com/questions/31610979/how-to-create-a-document-from-an-xml-string

The following steps might be one of the solutions.

  1. Make XSD file from XML file.
    http://hakutoitoi.hatenablog.com/entry/20090125/1232816823
  2. Make a document type by import the XSD file.
  3. Map the outputs from “pub.xml.xmlNodeToDocument” to the document type.
  4. Recognize each value in the document and insert them to DB.

Best regards,
Shogo Tatsuzawa

Do you want to parse the XML and insert each element into its own column? Or do you just want to insert the entire XML into a single column?

Dear Percio-san,

Thanks for the reply very much.

Do you want to parse the XML and insert each element into its own column?
Or do you just want to insert the entire XML into a single column?

I wanted to parse the XML and insert each value into the DB tables.

At present, we successfully retrieve the values in the xml by the configuration of hierarichical “LOOP” flow step
according to the above step.

Best regards,
Shogo Tatsuzawa

Hi Shogo,

you can use pub.xml:queryXMLNode to retrieve the values from the XML and present them as variables to the pipeline.

Another option is to convert the file to a xml string and convert this string to a document.
After that you can assign the elements from the document to the adapter service using direct mapping or variable substitution.

The neccessary services can be found in WmPublic-Package under pub.xml-Folder.

See IS-Build-In-Services-Guide for further informations.

Regards,
Holger

Holger-san,

Thanks for the reply very much.

you can use pub.xml:queryXMLNode to retrieve the values from the XML and
present them as variables to the pipeline.

I also just understand that the xml file is converted to the document including
the appropriate sets of valuable names and values even without making
the document type from xsd file previously.

1)pub.file:getFile 2)pub.xml:xmlStringToXMLNode 3)pub.xml:queryXMLNode 4)pub.xml:xmlnodeToDocument

Let me confim one thing.
You have tips about to retrieve the value in the output document at step 4
to insert to DB without mapping the predefined document type based xsd file?

At the pipeline output of step 4, I cannot specify the valuable names and values
because there is only “documents”

So, when mapping the values after step 4 to use “LOOP”, I suppose the predefined
document type based on xsd would be necessary to map. Am I right?

Best regards,
Shogo Tatsuzawa

Hi Shogo,

if you have a XSD for the Document this will ease things a lot.

Create DocumentType for it and specify the name in the appropriate field in xmlNodeToDocument.
Map the documents output to a Reference of the DocumentType.

queryXMLNode and xmlNodeToDocument are alternatives for getting acces to the values.
Depends on the complexity of the document strucuture.

Can you share a sample of the document structure as well as the input signature of the adapter service?

Regards,
Holger

Dear Holger-san,

if you have a XSD for the Document this will ease things a lot.

Really? It seemed to be no difference between the output values of following (A) and (B).
In details, please refer to the attached sheet.

(A) “Pure” Outputs of “documents” from “pub.xml:xmlNodeToDocument”
(B) Mapped Outputs from “pub.xml:xmlNodeToDocument” in the document reference
which is linked to the document type made from XSD.

Create DocumentType for it and specify the name in the appropriate field in xmlNodeToDocument.
Map the documents output to a Reference of the DocumentType.

You mean as below?
(1) Do not use XSD to make the document type for the XML
(2) It is better to make the document type manually from XML

If so, please sincerely how to do (2).

queryXMLNode and xmlNodeToDocument are alternatives for getting acces to the values.
Depends on the complexity of the document strucuture.

I confirmed that the result of both build-in services are same.

Can you share a sample of the document structure as well as the input signature of the adapter service?

The XML file includes some confidential data, I am sorry I could not send the XML.
And I do not made adapter services yet because I still focus on how to retrieve the data from XML most easily.

Best regards,
Shogo Tatsuzawa

xmltodoc_20151027.zip (892 KB)

Hi Shogo,

It is always better to use an XSD for creating the document instead of an XML instance as the XSD knows about the constraints and types of the fields which is not part of the XML instance.

For assisting with the query only the structrure is needed, not the content.
You can remove it from the sample but leave the structure intact.
If the structure is also confindential then this will make things more difficult.

The more complex the structure it will be easier to go for xmlNodeToDocument, even when retrieving a lot of fields.
queryXMLNode is easier to handle when retrieving only a few fields out of large documents.
This is also a performance question.

Regards,
Holger

Dear Holger-san,

Thanks for the reply very much.

It is always better to use an XSD for creating the document instead of an XML instance as the XSD
knows about the constraints and types of the fields which is not part of the XML instance.

Today, I created the XSD file from XML using the following web tool.
http://www.freeformatter.com/xsd-generator.html

Please comment about using this web page.

Best regards,
Shogo Tatsuzawa