Skip to main content

Anyone have a suggestion about how do i use this XML to ingest data into ODX?

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<ExportDocsProjectWithTendersResponse xmlns="http://publicprocurement.com">
<ExportDocsProjectWithTendersResult>{
"ProjectBodies": [
{
"ExternalId": "666666",
"DefaultEvaluationModel": "LowestPrice",
"DocumentNumberingSchema": "Numeric",
"SectionNumberingSchema": 0,
"RequirementNumberingSchema": "Alphabetic",
"Metadata": [
{
"Name": "ProjectName",
"Value": "Ingesting Data into TX"
},
{
"Name": "Reference",
"Value": "123456"
},
{
"Name": "ContractType",
"Value": "XML"
}
],
"DataFields": [
{
"ExternalId": "1",
"Type": "FreeText",
"Name": "Prosjct Name",
"Category": "C1",
"Value": "V1"
},
{
"ExternalId": "2",
"Type": "FreeText",
"Name": "Prosjct Name 2",
"Category": "C2",
"Value": "V2"
},
{
"ExternalId": 3,
"Type": "FreeText",
"Name": "Prosjct Name 3",
"Category": "C3",
"Value": "V3"
},
}
]
}
</ExportDocsProjectWithTendersResult>
</ExportDocsProjectWithTendersResponse>
</soap:Body>
</soap:Envelope>

I have come this far:

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<!-- See Column Definitions to specify column behavior and use XPaths to extract column values from XML. -->
<api:info title="contracts/contract" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">

<attr name="ExternalId" xs:type="string" readonly="false" other:xPath="/Envelope/Body/ExportDocsProjectWithTendersResponse/ExportDocsProjectWithTendersResult" />

</api:info>

<api:set attr="DataModel" value="RELATIONAL" />
<api:set attr="URI" value="https://my.soap.provider.com/endpoint.asmx?WSDL" />
<api:set attr="PushAttributes" value="true" />
<api:set attr="EnablePaging" value="true" />
<api:set attr="Header:Name#" value="SOAPAction"/>
<api:set attr="Header:Value#" value="http://publicprocurement.com/ExportDocsProjectWithTenders"/>
<api:set attr="XPath" value="/Envelope/Body/ExportDocsProjectWithTendersResponse/ExportDocsProjectWithTendersResult" />

<!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
<api:script method="GET">
<api:set attr="method" value="POST"/>
<api:set attr="ContentType" value="text/xml"/>
<api:set attr="AcceptCharset" value="utf-8" />
<api:set attr="data">
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://publicprocurement.com">
<soap:Header/>
<soap:Body>
<pub:ExportDocsProjectWithTenders>
<pub:SystemUserID>123456</pub:SystemUserID>
<pub:ProjectID>666666</pub:ProjectID>
</pub:ExportDocsProjectWithTenders>
</soap:Body>
</soap:Envelope>
</api:set>
<api:call op="xmlproviderGet">
<api:push/>
</api:call>
</api:script>
</api:script>

When i run this. i get everything from ProjectBodies

I have tried this to splitt the result into columns of Metadata and Datafileds but this didnt gave me anythig

<attr name="Metadata"         			xs:type="string"  		readonly="false"   other:xPath="/Envelope/Body/ExportDocsProjectWithTendersResponse/ExportDocsProjectWithTendersResult/Metadata" />
<attr name="DataFields" xs:type="string" readonly="false" other:xPath="/Envelope/Body/ExportDocsProjectWithTendersResponse/ExportDocsProjectWithTendersResult/DataFields" />

 

 

Ideally, i would like to splitt everything under Metadata and DataField into columns. 
I am sort of stuck

Hi ​@aftabp 

Is the result in JSON and the input in XML?

Start by generating the RSD file based on the JSON result. Then the fields will have the right XPath and the XPath or JSONPath setup will point to where the data is.

Afterwards you can change the URI and similar to point at the real data. If there is a mix between XML and JSON, it will be hard to get it to read it properly as it requires different contenttype setups.


 

@Thomas Lind 
I have already tried before posting the question.

The output in POSTMAN is in XML.
I have converted it into JSON

           {"ProjectBodies": s
{
"ExternalId": "666666",
"DefaultEvaluationModel": "LowestPrice",
"DocumentNumberingSchema": "Numeric",
"SectionNumberingSchema": 0,
"RequirementNumberingSchema": "Alphabetic",
"Metadata": a
{
"Name": "ProjectName",
"Value": "Ingesting Data into TX"
},
{
"Name": "Reference",
"Value": "123456"
},
{
"Name": "ContractType",
"Value": "XML"
}
],
"DataFields": s
{
"ExternalId": "1",
"Type": "FreeText",
"Name": "Prosjct Name",
"Category": "C1",
"Value": "V1"
},
{
"ExternalId": "2",
"Type": "FreeText",
"Name": "Prosjct Name 2",
"Category": "C2",
"Value": "V2"
},
{
"ExternalId": 3,
"Type": "FreeText",
"Name": "Prosjct Name 3",
"Category": "C3",
"Value": "V3"
}
]
}
]
}
]

Then i got this error in TX

System.Data.CData.REST.RESTDataReader ExecuteDataReader(System.Data.CommandBehavior)
r500] Could not execute the specified command: Unable to retrieve columns for table bJSONData]. : "REST"."JSONData"

 


Hi ​@aftabp 

Why are you converting it to JSON, if it is XML why not add it like that?

I thought your source returned in JSON, but if it returns data in XML, that is what you should use.

You are able to get the correct result of your SOAP Call in Postman, right?


@Thomas Lind 
May be i was unclear 

What i ment was that i converted the XML into JSON from POSTMAN to find out XPath and it turned out that i was right all along about the path. 
The issue is that when i define it in RSD-file, the fields turn out empty 

Here is my RSD

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<!-- See Column Definitions to specify column behavior and use XPaths to extract column values from XML. -->
<api:info title="contract" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
<attr name="ExternalId" xs:type="string" readonly="false" other:xPath="/Envelope/Body/ExportDocsProjectWithTendersResponse/ExportDocsProjectWithTendersResult/ProjectBodies/ExternalId" />
<attr name="Name" xs:type="string" readonly="false" other:xPath="/Envelope/Body/ExportDocsProjectWithTendersResponse/ExportDocsProjectWithTendersResult/ProjectBodies/ProjectBodies/Metadata/Name" />


</api:info>

<api:set attr="DataModel" value="RELATIONAL" />
<api:set attr="URI" value="https://my.soap.endpoint.com/extention.asmx?WSDL" />
<api:set attr="PushAttributes" value="true" />
<api:set attr="EnablePaging" value="true" />
<api:set attr="Header:Name#" value="SOAPAction"/>
<api:set attr="Header:Value#" value="http://publicprocurement.com/ExportDocsProjectWithTenders"/>
<api:set attr="XPath" value="/Envelope/Body/ExportDocsProjectWithTendersResponse" />

<!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
<api:script method="GET">
<api:set attr="method" value="POST"/>
<api:set attr="ContentType" value="text/xml"/>
<api:set attr="AcceptCharset" value="utf-8" />
<api:set attr="data">
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://publicprocurement.com">
<soap:Header/>
<soap:Body>
<pub:ExportDocsProjectWithTenders>
<pub:SystemUserID>123456</pub:SystemUserID>
<pub:ProjectID>666666</pub:ProjectID>
</pub:ExportDocsProjectWithTenders>
</soap:Body>
</soap:Envelope>
</api:set>
<api:call op="xmlproviderGet">
<api:push/>
</api:call>
</api:script>
</api:script>

This is how table look like:

 


Reply