CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008sqlxml
Vikas Kunte
Vikas Kunte

Reputation: 731

How to parse XML attribute using MS SQL

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

  1. The values of "TradeNumber", "Volume", "DateTime" in trade tag

  2. "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

Upvotes: 1

Views: 851

Answers (2)

granadaCoder
granadaCoder

Reputation: 27852

Another variant:

declare @doc xml

select @doc= '
<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>
'



;WITH XMLNAMESPACES('www.somewebsite.com' AS p)
SELECT 
      ActionAttribute = Y.i.value('(@Action)[1]', 'varchar(40)')
    , TradeNumber = Y.i.value('@TradeNumber[1]', 'varchar(40)') 
    , Specifierid = Y.i.value('(./p:Specifier)[1]/@Specifierid', 'nvarchar(max)') 
FROM 
    @doc.nodes('/p:trade') AS Y(i)

Upvotes: 0

roman
roman

Reputation: 117380

declare @data xml ='
<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>'

;with xmlnamespaces(default 'www.somewebsite.com')
select 
    @data.value('trade[1]/@TradeNumber', 'int') as TradeNumber,
    @data.value('trade[1]/@Volume', 'int') as Volume,
    @data.value('trade[1]/@DateTime', 'datetime') as [DateTime],
    @data.value('(trade/Specifier)[1]/@Name', 'nvarchar(max)') as Name

--------------------------------------------------------
TradeNumber Volume  DateTime                Name
       1053     25  2013-12-06 10:22:47.497 Indian

Or, if there're could be more than one trades:

;with xmlnamespaces(default 'www.somewebsite.com')
select 
    t.c.value('@TradeNumber', 'int') as TradeNumber,
    t.c.value('@Volume', 'int') as Volume,
    t.c.value('@DateTime', 'datetime') as [DateTime],
    t.c.value('Specifier[1]/@Name', 'nvarchar(max)') as Name
from @data.nodes('trade') as t(c)

Upvotes: 1

Related Questions