20100129

Processing QBXML In SQL Server

I've been working with QuickBooks again, and this time I need to parse some QBXML in the database so I can do some data syncronization on a schedule.

SQL Server 2005+ has some nice XML tools that make things a lot easier.  I was having trouble getting things to show up, but eventually found some pretty basic examples.

I'm parsing an Invoice Query response.

Here is what I came up with:
-- variable to hold document reference
DECLARE @docHandle int

-- variable to hold actual XML
DECLARE @xmlDocument nvarchar(max) -- or xml type

-- Fill XML variable
SELECT TOP 1 @xmlDocument = QBXML FROM QBBridge ORDER BY QBBridgeID DESC

-- Use built in Procedure to process the XML into Data
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

    DECLARE @ParentID int
    DECLARE qbXMLCursor CURSOR FOR
        -- Get All InvoiceRet Objects.
        SELECT ID FROM OPENXML(@docHandle, N'/QBXML/QBXMLMsgsRs') WHERE NodeType = 1 AND LocalName = 'InvoiceRet'

    -- cycle through InvoiceRets and compile Sub Data.
    OPEN qbXMLCursor
    FETCH NEXT FROM qbXMLCursor INTO @ParentID

   
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Use Common Table Expression to compile values to names of parent nodes
        -- I named my table columns <ParentName>.<ParentName>.<Name> so I'm sitting pretty for an insert from this.
        WITH InvoiceValues ([LocalName], [Value], [Level], [ID]) AS
        (
            SELECT    LocalName,
                    [text],
                    1 AS [Level],
                    [ID]
            FROM    OPENXML(@docHandle, N'/QBXML/QBXMLMsgsRs')
            WHERE ParentID = @ParentID
            UNION ALL
            SELECT    CASE WHEN SubValues.LocalName = '#text' THEN
                        InvoiceValues.LocalName
                    ELSE
                     InvoiceValues.LocalName + '.' + SubValues.LocalName
                     END As LocalName,
                    [text],
                    InvoiceValues.[Level] + 1 AS [Level],
                    SubValues.[ID]
            FROM    InvoiceValues
                    INNER JOIN OPENXML(@docHandle, N'/QBXML/QBXMLMsgsRs') As SubValues
                    ON InvoiceValues.ID = SubValues.ParentID
        )
        SELECT    [LocalName], [Value]
        FROM    InvoiceValues
        WHERE Value IS NOT NULL
           
        FETCH NEXT FROM qbXMLCursor INTO @ParentID
    END
    CLOSE qbXMLCursor
    DEALLOCATE qbXMLCursor

EXEC sp_xml_removedocument @docHandle