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:
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
