SQL XML Parsing

Transact-SQL
1

I have a xml structure:

15

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.

Thanks

2

Please post a small sample of your input XML