I need to get the values of attributes present in XML document using MS SQL query
Ex : I have a XML which looks below
<trade xmlns="www.somewebsite.com" Action = "Insert" TradeNumber = "1053" Volume = "25" DateTime = "2013-12-06T10:22:47.497" PNC = "false">
<Specifier Specifierid = "112" Span = "Single" Name = "Indian"/>
</trade>
I need to fetch
The values of "TradeNumber", "Volume", "DateTime" in trade tag
"Name" from Specifier tag
in a single row under their specific columns
Like
TradeNumber Volume DateTime Name
1053 25 2013-12-06T10:22:47.497 Indian
I tried using many ways but couldn't figure it out. Please help
Or, if there're could be more than one trades: