How to use zBar generated XML in SQL

I'm using zBar to read QR codes from scanned image files and am trouble parsing the XML that zBar generates in my SQL scripts.

For background, zBar is an open source barcode reading tool. In addition to the source code for use in your own projects, there is a command line tool (zBarImg.exe) to read barcodes (including QR Codes) from image files. I have zBar working great so this question is specificly about how to use the XML it generates in my SQL scripts.

I'm using the ZBar command line utility (zbarimg) to read images that have two qr codes on each image and it produces this xml file:

<barcodes xmlns="http://zbar.sourceforge.net/2008/barcode">
  <source href="C:\BC\SCanner\2016_03_12_14_19_44.jpg">
    <index num="0">
      <symbol type="QR-Code" quality="1">
        <data>F01868</data>
      </symbol>
      <symbol type="QR-Code" quality="1">
        <data>TC16-A397</data>
      </symbol>
    </index>
  </source>
  <source href="C:\BC\SCanner\2016_03_12_14_19_46.jpg">
    <index num="0">
      <symbol type="QR-Code" quality="1">
        <data>F01869</data>
      </symbol>
      <symbol type="QR-Code" quality="1">
        <data>TC16-A397</data>
      </symbol>
    </index>
  </source>
  <source href="C:\BC\SCanner\2016_03_12_14_19_48.jpg">
    <index num="0">
      <symbol type="QR-Code" quality="1">
        <data>F01870</data>
      </symbol>
      <symbol type="QR-Code" quality="1">
        <data>TC16-A397</data>
      </symbol>
    </index>
  </source>
</barcodes>

This works great and I can see all the information I need. For example on the first image I get the file name and the two qr codes:

<source href="C:\BC\SCanner\2016_03_12_14_19_46.jpg">
<symbol type="QR-Code" quality="1"><data>F01868</data>
<symbol type="QR-Code" quality="1"><data>TC16-A397</data>

I'm having trouble parsing / reading this into a usable table.

I think my problem is trying to get first an element and then nodes... but I also am confused why they the index is always zero and it just generally seems a strange way to structure the xml to me.

Solution

OK, so I worked out a way to do this that worked for my needs so I wanted to share that... and also see if there are any more elegant solutions the community could come up with.

Create a table and pull the xml into SQL. I am actually doing a batch of many xml files at once, so oversimplified it here:

CREATE TABLE [dbo].[zBar_Batches](
        [ZBatchID] [int] IDENTITY(1,1) NOT NULL,
        [Filename] [nvarchar](255) NULL,
        [BarCodeXML] [xml] NULL,
        [Status] [nvarchar](50) NULL
        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

        INSERT INTO zBar_Batches (Filename, BarCodeXML, Status) Values ('C:\BC\Logs\barcodes.xml', '', 'New Batch')
        UPDATE zBar_Batches SET BarCodeXML =(SELECT * FROM OPENROWSET(BULK 'C:\BC\Logs\barcodes.xml', SINGLE_BLOB ) AS x ) WHERE Filename = 'C:\BC\Logs\barcodes.xml'

What had me hung up for several days was right in front of my eyes the whole time... I completely forgot to set the default NAMESPACE. Once I cracked that, things started going much better. I was so used to ignoring the first line of the xml file, or just looking at the encoding. I need to use XMLNAMESPACES(DEFAULT 'http://zbar.sourceforge.net/2008/barcode') which was a lesson learnd on this one.

Once I had that squared away, all the research I had done on Stack Overflow started working and the best I came up with was this. I will be the first to admit the cross tab select query was cobbled together from a couple of great overflow threads as that was way beyond my native abilities at the time.

IF OBJECT_ID('tempdb..#Pages') IS NOT NULL DROP TABLE #Pages
    CREATE TABLE #Pages(
        [zbarID] [int] IDENTITY(1,1) NOT NULL,
        [FilePath] [varchar](255) NULL,
        [QRcode] [varchar](50) NULL
    )

DECLARE @zXML xml

SELECT @zXML = BarCodeXML from zBar_Batches where ZBatchID= @BatchID and BarCodeXML is not null
;WITH XMLNAMESPACES(DEFAULT 'http://zbar.sourceforge.net/2008/barcode') 
INSERT INTO #Pages
select batch.src.value('@href','varchar(255)') as FilePath,
    batch1.sym.value('data[1]','varchar(50)') as QRcode
    from @zXML.nodes('/barcodes/source') as batch(src)
    cross apply batch.src.nodes('index/symbol') as batch1(sym)

This gave me six rows... one for each barcode (2 on each of 3 image).

zbarID  FilePath                                QRcode
------- --------------------------------------- -----------
1       C:\BC\SCanner\2016_03_12_14_19_44.jpg   F01868
2       C:\BC\SCanner\2016_03_12_14_19_44.jpg   TC16-A397
3       C:\BC\SCanner\2016_03_12_14_19_46.jpg   F01869
4       C:\BC\SCanner\2016_03_12_14_19_46.jpg   TC16-A397
5       C:\BC\SCanner\2016_03_12_14_19_48.jpg   F01870
6       C:\BC\SCanner\2016_03_12_14_19_48.jpg   TC16-A397

Of course I still have some validation work to do and need to rely on the format of the qrcode value to know which code it was, but came together pretty quickly,

I hope this helps anyone else trying to use zBar xml with a sql. I'd love to see any feed back on the final query.

If you are interested, zBar also has a command line to read from a webcam attached to the computer. The zBarImg utility worked very well for me. It was able to read both QR codes accurately in 98% of the almost 1500 images I used it for.