blackhummer
New Member
Hi I have the following SQL to try and parse xml and extract the "OrderNumber". The problem i have is this xml (which i have no control over) has a wierd xml namespace. I changed it to abc.com just for this example, but its something else. Anyway, when that namepace is present, the T-SQL returns a null in the result. So it doesn't play nicely with the namespace. If I remove the namespace manually or doing a search and replace via T-SQL, it works just fine. I guess i can just do a search and replace but that solution just bothers me. Was wondering if anyone else nows a better way around this? And maybe an explanation of why it doesn't like namespaces? Would really appreciate some advice. Thanks!\[code\]Declare @Transmission xmlset @Transmission = '<Transmission> <Requests> <SubmitOrdersRequest> <Orders> <Order xmlns="http://www.abc.com"> <OrderNumber>123</OrderNumber> </Order> </Orders> </SubmitOrdersRequest> </Requests></Transmission>'select @Transmission.value('(Transmission/Requests/SubmitOrdersRequest/Orders/Order/OrderNumber/text())[1]', 'varchar(100)')\[/code\]