How to parse XML attribute using MS SQL

Question

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

i couldn't get how to create table in question section

Vikas Kunte
– Vikas Kunte

2013-12-06 14:24:32 +00:00
Commented Dec 6, 2013 at 14:24 — Vikas Kunte
– Vikas Kunte, Commented Dec 6, 2013 at 14:24

roman · Accepted Answer · 2013-12-06 14:30:14Z

1

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)

answered Dec 6, 2013 at 14:30

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathese F Over a year ago

Faster than me :) You have the possibility to make research based on your attribute, instead of 1 you can specify contains(@attribute, "value"). Take care Value can always only give back one value

granadaCoder · Accepted Answer · 2013-12-06 14:35:05Z

0

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)

answered Dec 6, 2013 at 14:35

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

Collectives™ on Stack Overflow

How to parse XML attribute using MS SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related