I have a question that is very similar to this guys, http://social.msdn.microsoft.com/Forums/en-SG/sqlxml/thread/d8730c0f-4a0c-43bc-885c-5474f49174f4. I have XML in the very same format as his, with the ns0: before every node level. I have a table called @ProcessingXML with columns XMLData as a data type XML and TransactionSetNo as a data type varchar(20) with the XML file in the XMLData column. I want to be able to do this:

WITH XMLNAMESPACES
(
DEFAULT 'http://schemas.microsoft.com/BizTalk/EDI/X12/2006'
)

UPDATE
@ProcessingXML
SET
TransactionSetNo = XMLData.value('(//BHT_BeginningofHierarchicalTransaction/@BHT03_OriginatorApplicationTransactionIdentifier)[1]', 'VARCHAR(20)')

Currently, doing this doesn't pull any of the values from that particular element in the node. Any help would be greatly appreciated.

Submitted under: Microsoft SQL Server · XQUERY ·  ·  ·