0

I have several years experience working with JS, Python, and JSON, but just started working with C#.NET and XML within the past week.

I'm having to write a solution for Quickbooks Desktop to consolidate multiple invoice line items into one line item. The examples I have from Intuit are like +10 years old. It isn't entirely clear to me from SO topics what a modern approach is for my use case: either the answers are +6 years old or it has to do with reading from files--I'm trying to parse a response.

Here is what the server response looks like (I am temporarily writing it to a file) followed by some questions:

<QBXML>
  <QBXMLMsgsRs>
    <InvoiceQueryRs requestID="0" statusCode="0" statusSeverity="Info" statusMessage="Status OK">

      <!-- RECORD 1 -->
      <InvoiceRet>
        <TxnID>3B58B-1540309911</TxnID>
        <EditSequence>1540587328</EditSequence>
        <TxnNumber>27058</TxnNumber>
        <CustomerRef>
          <ListID>800006FF-1540307596</ListID>
          <FullName>Test Co.:Test 1</FullName>
        </CustomerRef>
        <RefNumber>L-9</RefNumber>
        <Subtotal>2212.00</Subtotal>
        <BalanceRemaining>2212.00</BalanceRemaining>
        <IsPaid>false</IsPaid>

        <!-- RECORD 1: INVOICE LINE ITEM 1 -->
        <InvoiceLineRet>
          <TxnLineID>3B58D-1540309911</TxnLineID>
          <ItemRef>
            <ListID>80000002-1443563699</ListID>
            <FullName>Consulting Fees</FullName>
          </ItemRef>
          <Desc>PROJECT: Test 1; TASK: Wrap-up</Desc>
          <Quantity>6</Quantity>
          <Rate>220</Rate>
          <Amount>1320.00</Amount>
        </InvoiceLineRet>

        <!-- RECORD 1: INVOICE LINE ITEM 2 -->
        <InvoiceLineRet>
          <TxnLineID>3B58E-1540309911</TxnLineID>
          <ItemRef>
            <ListID>80000002-1443563699</ListID>
            <FullName>Consulting Fees</FullName>
          </ItemRef>
          <Desc>PROJECT: Test 1; TASK: Kickoff</Desc>
          <Quantity>4</Quantity>
          <Rate>220</Rate>
          <Amount>880.00</Amount>
        </InvoiceLineRet>

        <!-- RECORD 1: INVOICE LINE ITEM 3 -->
        <InvoiceLineRet>
          <TxnLineID>3B58F-1540309911</TxnLineID>
          <ItemRef>
            <ListID>80000025-1538518495</ListID>
            <FullName>Travel:Travel Meals</FullName>
          </ItemRef>
          <Desc>DATE: Oct 23, 2018; PROJECT: Test 1</Desc>
          <Rate>12</Rate>
          <Amount>12.00</Amount>
        </InvoiceLineRet>
      </InvoiceRet>

      <!-- RECORD 2 -->
      <InvoiceRet>
        <TxnID>3B595-1540830324</TxnID>
        <EditSequence>1540830324</EditSequence>
        <TxnNumber>27060</TxnNumber>
        <CustomerRef>
          <ListID>80000700-1540307618</ListID>
          <FullName>Test Co.:Test 2</FullName>
        </CustomerRef>
        <RefNumber>L-11</RefNumber>
        <Subtotal>2760.00</Subtotal>
        <BalanceRemaining>2760.00</BalanceRemaining>

        <!-- RECORD 2: INVOICE LINE ITEM 1 -->
        <InvoiceLineRet>
          <TxnLineID>3B597-1540830324</TxnLineID>
          <ItemRef>
            <ListID>80000002-1443563699</ListID>
            <FullName>Consulting Fees</FullName>
          </ItemRef>
          <Desc>PROJECT: Test 2; TASK: Prebill Task</Desc>
          <Rate>1000</Rate>
          <Amount>1000.00</Amount>
        </InvoiceLineRet>

        <!-- RECORD 2: INVOICE LINE ITEM 2 -->
        <InvoiceLineRet>
          <TxnLineID>3B598-1540830324</TxnLineID>
          <ItemRef>
            <ListID>80000002-1443563699</ListID>
            <FullName>Consulting Fees</FullName>
          </ItemRef>
          <Desc>PROJECT: Test 2; TASK: Present Findings</Desc>
          <Quantity>3</Quantity>
          <Rate>0.00</Rate>
          <Amount>0.00</Amount>
        </InvoiceLineRet>

        <!-- RECORD 2: INVOICE LINE ITEM 3 -->
        <InvoiceLineRet>
          <TxnLineID>3B599-1540830324</TxnLineID>
          <ItemRef>
            <ListID>80000002-1443563699</ListID>
            <FullName>Consulting Fees</FullName>
          </ItemRef>
          <Desc>PROJECT: Test 2; TASK: Research</Desc>
          <Quantity>2</Quantity>
          <Rate>220</Rate>
          <Amount>440.00</Amount>
        </InvoiceLineRet>

        <!-- RECORD 2: INVOICE LINE ITEM 4 -->
        <InvoiceLineRet>
          <TxnLineID>3B59A-1540830324</TxnLineID>
          <ItemRef>
            <ListID>80000002-1443563699</ListID>
            <FullName>Consulting Fees</FullName>
          </ItemRef>
          <Desc>PROJECT: Test 2; TASK: Project Launch</Desc>
          <Quantity>6</Quantity>
          <Rate>220</Rate>
          <Amount>1320.00</Amount>
        </InvoiceLineRet>
      </InvoiceRet>

    </InvoiceQueryRs>
  </QBXMLMsgsRs>
</QBXML>
  1. In general, what is the modern approach to parsing this so that each element can be referred to?

Something like xml.InvoiceRet[0].TxnId which would be 3B58B-1540309911.

  1. How is each record referred to given that the parent node <InvoiceRet> is nondescript? Do you have to iterate through each <InvoiceRet>?

What I have tried from the Intuit documentation:

// this is where the query is sent to the QB and response is returned
var responseSet = sessionManager.DoRequests(requestSet);

// take the response and get the first element, I guess?
var response = responseSet.ResponseList.GetAt(0);
var customerRetList = response.Detail as ICustomerRetList;

// print the number of records
// it should be 2, but comes back with nothing
Console.WriteLine(customerRetList.Count);
cjones
  • 8,384
  • 17
  • 81
  • 175
  • Does your service uses WSDL? If so, all you need to do is create a service proxy client. Either use VisualStudio to add a Service Reference, or use **svcutil** command to do so. It will generate your proxy classes. – Alfredo A. Oct 29 '18 at 18:33
  • It sounds like if I was using the Web Connector, WSDL be an option. This is just a desktop solution that runs on the same machine the user has QuickBooks desktop open in. – cjones Oct 29 '18 at 19:52

2 Answers2

1

I put results into a datatable so results are flat. I got results from a file. The Load method with work with either a URL or filename. Replace Load() with Parse() if you have a string.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("TxnID", typeof(string));
            dt.Columns.Add("EditSequence", typeof(string));
            dt.Columns.Add("TxnNumber", typeof(string));
            dt.Columns.Add("CustomerRef_ListID", typeof(string));
            dt.Columns.Add("CustomerRef_FullName", typeof(string));
            dt.Columns.Add("RefNumber", typeof(string));
            dt.Columns.Add("Subtotal", typeof(decimal));
            dt.Columns.Add("BalanceRemaining", typeof(decimal));
            dt.Columns.Add("IsPaid", typeof(Boolean));
            dt.Columns.Add("ItemRef_ListID", typeof(string));
            dt.Columns.Add("ItemRef_FullName", typeof(string));
            dt.Columns.Add("Desc", typeof(string));
            dt.Columns.Add("Quantity", typeof(int));
            dt.Columns.Add("Rate", typeof(decimal));
            dt.Columns.Add("Amount", typeof(decimal));


            XDocument doc = XDocument.Load(FILENAME);

            foreach (XElement invoiceRet in doc.Descendants("InvoiceRet"))
            {
                string txnId = (string)invoiceRet.Element("TxnID");
                string editSequence = (string)invoiceRet.Element("EditSequence");
                string txnNumber = (string)invoiceRet.Element("TxnNumber");
                XElement customerRef = invoiceRet.Element("CustomerRef");
                string custListId = (string)customerRef.Element("ListID");
                string custFullName = (string)customerRef.Element("FullName");
                string refNumber = (string)invoiceRet.Element("RefNumber");
                decimal subtotal = (decimal)invoiceRet.Element("Subtotal");
                decimal balance = (decimal)invoiceRet.Element("BalanceRemaining");
                Boolean? isPaid = (Boolean?)invoiceRet.Element("IsPaid");

                foreach (XElement invoiceLine in invoiceRet.Elements("InvoiceLineRet"))
                {
                    string lineListId = (string)invoiceLine.Descendants("ListID").FirstOrDefault();
                    string lineFullName = (string)invoiceLine.Descendants("FullName").FirstOrDefault();
                    string desc = (string)invoiceLine.Element("Desc");
                    int? quantity = (int?)invoiceLine.Element("Quantity");
                    decimal rate = (decimal)invoiceLine.Element("Rate");
                    decimal amount = (decimal)invoiceLine.Element("Amount");

                    dt.Rows.Add(new object[] {
                        txnId, editSequence, txnNumber, custListId, custFullName,
                        refNumber, subtotal, balance, isPaid, 
                        lineListId, lineFullName, desc, quantity, rate, amount 
                    });


                }
            }
        }
    }
}
jdweng
  • 33,250
  • 2
  • 15
  • 20
0

It seems like for my use case, Basic Query (LINQ to XML) would be the best option. Decided to parse the response to a string using:

// Do the request and get the response message set object
var responseSet = sessionManager.DoRequests(requestSet);

// Convert response to string
var responseXml = responseSet.ToXMLString();

// Then the LINQ to XML for the string that I'm still working through
var xmlDoc = XDocument.Parse(responseXml);

// Set the xmlDoc root
var xmlDocRoot = xmlDoc.Root.Element("QBXMLMsgsRs")
                            .Element("InvoiceQueryRs")
                            .Elements("InvoiceRet");


// Iterate through the elements to get values and do some logic
foreach (var invoiceElement in xmlDocRoot)
{
    var editSequence = (string) invoiceElement.Element("EditSequence");
}
cjones
  • 8,384
  • 17
  • 81
  • 175