Query XML with multiple XMLNS

1

HI Guys please help me writing a qry

I've xml column in sql server database . The xml looks like the following

citrim xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance
Subject xmlns="http://jet.crew.mil.fit.mils.uk/">Physics /Subject
Marks xmlns="http://jek.crew.mil.fit.millpros.uk/"
Grade xmlns="http://jin.crux.c1.ac.ppp.uk/">A</Grade
Subject
citrim

For some reason this editor does not allow me to post xml tag. so accept my apologies for this inconvenience

I need to find the value of Grade. How would i do in T-SQL

Thank you so much

2

See here for examples. You write the qureies like in the example below taken from that page:

WITH XMLNAMESPACES ('uri1' as ns1, 
                    'uri2' as ns2,
                    DEFAULT 'uri2')
SELECT ProductID, 
      Name,
      Color
FROM Production.Product 
WHERE ProductID=316 or ProductID=317
FOR XML RAW ('ns1:Product'), ROOT('ns2:root'), ELEMENTS

You can designate one namespace as the default as in the example. For elements in that namespace, you don't have to prefix the namespace alias when referring to them. For all other namespaces you should refer to them prefixed with the namespace alias and a colon.

3

Thanks JamesK

I already have xml stores in a column and I am not getting success in retrieving the value from GRADE tag in my example above.

see I have many namespaces in my example attached with each tag... any idea how to do it ?

4

That doesn't look like well-formed XML. Can you post the actual XML that you are trying to shred?