Trying to xml stored record in a SQL DB.

Using Iway 5.6 how do you pull the XML that is store in a field.

I can read the record using SQL object then I need to read that xml in one of the fields.

 <Target_Root>
<row><NewDataSet><xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="NewDataSet"><xs:element name="NewDataSet" msdata:IsDataSet="true"><xs:complexType>
<xs:choice maxOccurs="unbounded"><xs:element name="HeaderTable"><xs:complexType><xs:sequence><xs:element name="Header_ID" type="xs:int" minOccurs="0"/><xs:element name="Check" type="xs:long" minOccurs="0"/><xs:element name="Supplier" type="xs:string" minOccurs="0"/><xs:element name="Supplier_Name" type="xs:string" minOccurs="0"/><xs:element name="Date" type="xs:dateTime" minOccurs="0"/><xs:element name="Eff_Date" type="xs:dateTime" minOccurs="0"/><xs:element name="Curr" type="xs:string" minOccurs="0"/><xs:element name="doc_nbr" type="xs:long" minOccurs="0"/><xs:element name="addr_code" type="xs:string" minOccurs="0"/><xs:element name="fs_field1" type="xs:dateTime" minOccurs="0"/><xs:element name="fs_field6" type="xs:long" minOccurs="0"/><xs:element name="fs_field2" type="xs:string" minOccurs="0"/>
<xs:element name="fs_field3" type="xs:string" minOccurs="0"/><xs:element name="fs_filed4" type="xs:long" minOccurs="0"/></xs:sequence></xs:complexType></xs:element><xs:element name="DetailTable"><xs:complexType><xs:sequence><xs:element name="Detail_ID" type="xs:int" minOccurs="0"/><xs:element name="Entity" type="xs:string" minOccurs="0"/><xs:element name="Voucher" type="xs:string" minOccurs="0"/><xs:element name="PO_Nbr" type="xs:string" minOccurs="0"/><xs:element name="Invoice_Number" type="xs:string" minOccurs="0"/><xs:element name="Account" type="xs:string" minOccurs="0"/><xs:element name="Sub_Acct" type="xs:string" minOccurs="0"/><xs:element name="CC" type="xs:string" minOccurs="0"/>
<xs:element name="Gross_Amount" type="xs:double" minOccurs="0"/><xs:element name="Discount_Amount" type="xs:double" minOccurs="0"/><xs:element name="Amount" type="xs:double" minOccurs="0"/>
<xs:element name="Remark" type="xs:string" minOccurs="0"/></xs:sequence></xs:complexType></xs:element><xs:element name="FooterTable"><xs:complexType><xs:sequence><xs:element name="Footer_ID" type="xs:int" minOccurs="0"/><xs:element name="Total_Gross_Amount" type="xs:double" minOccurs="0"/>
<xs:element name="Total_Disc_Amount" type="xs:double" minOccurs="0"/><xs:element name="Total_Amount" type="xs:double" minOccurs="0"/></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema><HeaderTable><Header_ID>2</Header_ID><Check>991055</Check>
<Supplier>00056017</Supplier><Supplier_Name>AIR CLAIR TRUCKING LTD</Supplier_Name><Date>2008-11-05T00:00:00.0000000-08:00</Date><Eff_Date>2008-11-05T00:00:00.0000000-08:00</Eff_Date><Curr>CDN</Curr><doc_nbr>991055</doc_nbr><addr_code>00056017</addr_code><fs_field1>2008-11-05T00:00:00.0000000-08:00</fs_field1><fs_field6>991055</fs_field6><fs_field2>AIR CLAIR TRUCKING LTD</fs_field2><fs_field3>CDN</fs_field3><fs_filed4>20</fs_filed4></HeaderTable><DetailTable><Detail_ID>2</Detail_ID><Entity>20</Entity><Voucher>738045</Voucher><PO_Nbr/><Invoice_Number>1836</Invoice_Number><Account>2000</Account><Sub_Acct/><CC/><Gross_Amount>3031.82</Gross_Amount><Discount_Amount>0</Discount_Amount><Amount>3031.82</Amount><Remark/></DetailTable><DetailTable><Detail_ID>2</Detail_ID><Entity>20</Entity><Voucher>738870</Voucher><PO_Nbr/><Invoice_Number>1852</Invoice_Number><Account>2000</Account><Sub_Acct/><CC/><Gross_Amount>3266.73</Gross_Amount><Discount_Amount>0</Discount_Amount><Amount>3266.73</Amount><Remark/></DetailTable><FooterTable><Footer_ID>2</Footer_ID><Total_Gross_Amount>6298.55</Total_Gross_Amount><Total_Disc_Amount>0</Total_Disc_Amount><Total_Amount>6298.55</Total_Amount></FooterTable></NewDataSet></row>
</Target_Root> 

This message has been edited. Last edited by: ray_In_Vancouver,

Hi,

Am I correct if I say that you need to read some values from that document you get as a result from sql object?

In that case define after sql object service object with class of XDSREGAgent and use there IFL function _flatof() which will store the whole returned document in SREG. And after that you could use XPATH for the needed results.

_flatof() function

In case you need some sample pflow let me know, but i will be able to create it maybe tomorrow evening.

Br,
Rivo