Convert from SQL to XML?

Question

I was struggling to convert this to XML, could someone please advise to display same information but this time in XML.

SQL is below

select so.ordernumber, 
       sod.productidname, 
       so.statuscode, 
       so.statuscodename, 
       sod.quantity, 
       sod.quantityshipped, 
       ip.pcssu_quantityavailable, 
       so.pcssu_stockavailable     
from FilteredSalesOrder so
inner join FilteredSalesOrderDetail sod 
    on sod.salesorderid = so.salesorderid
inner join filteredaccount a 
    on a.accountnumber = so.pcssu_servicecentrecode
inner join Filteredpcssu_inventoryproduct ip 
    on ip.pcssu_product = sod.productid 
    and ip.pcssu_servicecenter = a.accountid
where so.statuscode = '141560004'
and ip.pcssu_quantityavailable >= sod.quantity - sod.quantityshipped

I managed to convert most of the above to XML just without the and statement, the converter threw an error up mentioning

Error: Only conditions for the primary entity are supported in WHERE clause, consider moving the conditions for alias 'ip' to its JOIN's ON clause. Please note not all SQL queries can be converted to FetchXML due to the limitations of FetchXML query.

Please advise perhaps moving the and to joing the and statement thats throwing the error is

where so.statuscode = '141560004'
and ip.pcssu_quantityavailable >= sod.quantity - sod.quantityshipped

Below is converted XML without AND

<fetch mapping="logical" version="1.0">
  <entity name="SalesOrder">
    <attribute name="ordernumber" />
    <attribute name="statuscode" />
    <attribute name="statuscodename" />
    <attribute name="pcssu_stockavailable" />
    <filter>
      <condition attribute="statuscode" operator="eq" value="141560004" />
    </filter>
    <link-entity name="SalesOrderDetail" from="salesorderid" to="salesorderid" alias="sod" link-type="inner">
      <attribute name="productidname" />
      <attribute name="quantity" />
      <attribute name="quantityshipped" />
      <link-entity name="pcssu_inventoryproduct" from="pcssu_product" to="productid" alias="ip" link-type="inner">
        <attribute name="pcssu_quantityavailable" />
      </link-entity>
    </link-entity>
    <link-entity name="account" from="accountnumber" to="pcssu_servicecentrecode" alias="a" link-type="inner" />
  </entity>
</fetch>

Could you give an example of what you want the output to look like? Also, you say you were struggling - does that mean you have some attempts you could show us? Are you trying to do this within the SQL, or using some other programming language? — IMSoP, Dec 15 '16 at 13:01
Possible duplicate of [Add AND statement to WHERE clause in FETCH XML?](https://stackoverflow.com/questions/41181726/add-and-statement-to-where-clause-in-fetch-xml) — Arun Vinoth-Precog Tech - MVP, Jan 18 '18 at 20:46

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

There is a simple xml output as part of mysql command line tool - see here - XML output from MySQL

Command line parameter --xml - but output looks like this <row><field name="id">1</field><field name="name">myname</field></row>

edited May 23 '17 at 12:30

Community

1
1

answered Dec 15 '16 at 13:19

JosMac

2,164
1
17
23

score 0 · Answer 2 · answered Jan 18 '18 at 20:29

ip.pcssu_quantityavailable >= sod.quantity - sod.quantityshipped

Basically this kind of arithmetic formula based filters cannot be achieved in fetchxml. Either you need Rollup fields or Calculated field in some cases to achieve the same.

Otherwise you can fetch the resultset & filter/calculate in the code on your own.

Convert from SQL to XML?

2 Answers2