C# and QBXML SDK

I am finishing up a project that syncs an order system
using SQL Server to the invoice system in Quickbooks 2007. Most of this information is available in various locations on the internet, but it took me way longer to finish this project than I thought it should because of all the small issues I ran
into along the way. So I am posting this in case some one else runs into this same issue.

If you have already worked with the QBXML SDK, you already know that Quickbooks documentation is severely lacking in samples, and even general documentation on the layout of the XML requests leaves the programmer without
a lot to work with. Maybe this post will shed some light on some questions for you.

The Project

The requirements were pretty simple:

1. Retrieve invoices from SQL Server
2. Check if company exists in Quickbooks, if not create company
3. Check if invoice exists in Quickbooks, if not create, if it does throw duplicate exception
4. Update invoices in SQL Server so they are not added again
5. Create report of successfully imported invoices, duplicate invoices and invoice that could not be imported

The Issues

1. The 'Quickbooks found an error parsing the XML text stream' error.
2. The qbxml version of the request

This generic error message gave me fits through the development cycle. I couldn't find anything in the returned error that gave me a line number, a node that was incorrect for some reason, or any other information.

The XML Validator that is provided with the SDK did provide some help, it is definitely worth using. The problem that kept causing me issues was that the nodes have to be in a specific order (and I didn't find this piece of information until I was almost finished with the project) or you will get the parsing error, but I was unable to find any documentation that listed all of the available nodes and the specific order. I used trial and error to determine the correct order of the nodes I needed to send to Quickbooks.

The qbxml version processing instruction tripped me up a few times, but the error message it returned was specific enough that I could fix fairly easily.

The Code

I used the XML.Linq.XDocument to create my basic request and added nodes as needed when adding a customer or invoice:

• Customer Add

   XDocument xdoc = new XDocument(
    new XDeclaration("1.0", null, null),
       new XProcessingInstruction("qbxml", "version=\"2.0\""),
          new XElement("QBXML",
             new XElement("QBXMLMsgsRq",
                new XAttribute("onError", "stopOnError"),
                new XElement("CustomerAddRq",
                   new XAttribute("requestID", "1"),
                   new XElement("CustomerAdd",
                      new XElement("Name", agency.Name),
                      new XElement("CompanyName", agency.Name)
                   )
                )
             )
          )
      );

• Invoice Add

     XDocument xdoc = new XDocument(
      new XDeclaration("1.0", null, null),
         new XProcessingInstruction("qbxml", "version=\"2.0\""),
            new XElement("QBXML",
               new XElement("QBXMLMsgsRq",
                  new XAttribute("onError", "stopOnError"),
                     new XElement("InvoiceAddRq",
                        new XAttribute("requestID", "99"),
                        new XElement("InvoiceAdd",
                           new XElement("CustomerRef",
                              new XElement("FullName", invoice.AgencyName)
                           )
                        )
                    )
                )
            )
         );

The XML to check if customers or invoices existed didn't need to be modified after
the fact so I created it inline:

var reqXML = string.Format("<?xml version="1.0"?><?qbxml version="6.0"?><?qbxml version="2.0"?><QBXML><QBXMLMsgsRq onError="stopOnError"> <customerqueryrq requestid="\"><fullname>{0}</fullname></customerqueryrq></qbxmlmsgsrq></qbxml>", customerName);

var reqXML = string.Format("<?xml version="1.0"?><?qbxml version="6.0"?><QBXML><QBXMLMsgsRq onError="stopOnError"><invoicequeryrq requestid="\">";

reqXML += string.Format("<txndaterangefilter><fromtxndate>{0}</fromtxndate><totxndate>{0}</totxndate></txndaterangefilter><entityfilter><fullname>{1}</fullname></entityfilter></invoicequeryrq></qbxmlmsgsrq></qbxml>",invoiceDate,agencyName);

My QBXML Requests

• Customer Check

<?xml version="1.0"?>
<?qbxml version="6.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
<customerqueryrq requestid="\">
<fullname>Company Name</fullname>
</customerqueryrq>
</qbxmlmsgsrq>
</qbxml>


• Add Customer

<?xml version="1.0"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
<CustomerAddRq requestID="1"><CustomerAdd>
<Name>Company Name</Name>
<CompanyName>Company Name</CompanyName>
<BillAddress>
<Addr1>1200 Street Lane</Addr1>
<City>Dallas</City>
<State>TX</State>
<PostalCode>75230</PostalCode>
</BillAddress>
<Phone>555-555-1212</Phone>
</CustomerAdd>
</CustomerAddRq>
</QBXMLMsgsRq>
</QBXML>

• Invoice Check

<?xml version="1.0"?>
<?qbxml version="6.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
<invoicequeryrq requestid="\">
<txndaterangefilter>
<fromtxndate>2009-07-01</fromtxndate>
<totxndate>2009-07-01</totxndate>
</txndaterangefilter>
<entityfilter>
<fullname>Company Name</fullname>
</entityfilter>
</invoicequeryrq>
</qbxmlmsgsrq>
</qbxml>


• Add Invoice

<?xml version="1.0"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
<InvoiceAddRq requestID="99">
<InvoiceAdd>
<CustomerRef>
<FullName>Home Health Agency 139 - test6</FullName>
</CustomerRef>
<TxnDate>2008-12-31</TxnDate>
<CustomerMsgRef>
<FullName>Thank you for your business!</FullName>
</CustomerMsgRef>
<InvoiceLineAdd>
<ItemRef>
<FullName>Daily Visit PT</FullName>
</ItemRef>
<Desc>
Physical Therapist Daily Visit
Ada Feelbad (Fort Worth) 12/23/08
Betty Feelbad (Fort Worth) 12/06/08 12/15/08 12/17/08 12/21/08 12/22/08
Harold Feelbad (Fort Worth) 12/06/08 12/23/08
</Desc>
<Quantity>8</Quantity>
<Rate>102.00</Rate>
<Amount>816.00</Amount>
</InvoiceLineAdd>
<InvoiceLineAdd>
<ItemRef>
<FullName>Eval PT</FullName>
</ItemRef>
<Desc>
Physical Therapist Evaluation
Ada Feelbad (Fort Worth) 12/15/08
Harold Feelbad (Fort Worth) 12/20/08
</Desc>
<Quantity>2</Quantity>
<Rate>100.00</Rate>
<Amount>200.00</Amount>
</InvoiceLineAdd>
<InvoiceLineAdd>
<ItemRef>
<FullName>Missed PT Visit </FullName>
</ItemRef>
<Desc>
Missed Visit Physical Therapist
Ada Feelbad (Fort Worth) 12/24/08
</Desc>
<Quantity>1</Quantity>
<Rate>0.00</Rate>
<Amount>0.00</Amount>
</InvoiceLineAdd>
</InvoiceAdd>
</InvoiceAddRq>
</QBXMLMsgsRq></QBXML>

Hopefully this will help save you some time using the QBXML SDK in Visual Studio.