SQLTeam.com | Weblogs | Forums

SQL XML Parsing


I have a xml structure:


I need to get the Status value from the element. In the above example I would like to get the value Deleted. I have tried a couple different ways using exists.

  	/* update sync IDs on customer master */
        WITH 		XMLNAMESPACES ( DEFAULT 'http://schema.intuit.com/finance/v3' )
        Select		BA.value('Id[1]', 'varchar(20)')
        FROM        dbQBO.dbo.QBOXMLPush AS T INNER JOIN
                    PEService.dbo.Invoices ON T.Entity_ID = PEService.dbo.Invoices.Invoice_ID
                    CROSS APPLY T.xml_text.nodes('/IntuitResponse/BatchItemResponse/Invoice') A ( BA )
        WHERE  		t.Sub_ID = '559'
        			and t.xml_type = 'deleteInvoices'
                    and BA.exist('//Invoice/Status/[text()="Deleted"]') = 1

Any help would be appreciated.



Please post a small sample of your input XML