Xml Query

Transact-SQL
1

I have xml query I want to extract all into table s please help me
"




<account_id>199720851</account_id>
spps
<hero_id>21</hero_id>
0



0.000000
<roshan_respawn_timer>0</roshan_respawn_timer>

0

<lobby_id>24321722127977571</lobby_id>
<match_id>1992687278</match_id>
3
<series_id>0</series_id>
<game_number>0</game_number>
<league_id>3706</league_id>
<stream_delay_s>300.000000</stream_delay_s>
<radiant_series_wins>0</radiant_series_wins>
"

2

Please post your XML query, samples of input data, destination table definition

3

<result> <games> <game> <players> <player> <account_id>199720851</account_id> <name>sps</name> <hero_id>21</hero_id> <team>0</team> </player> </players> <scoreboard> <duration>0.000000</duration> <roshan_respawn_timer>0</roshan_respawn_timer> <radiant> <score>0</score> </scoreboard> <lobby_id>24321722127977571</lobby_id> <match_id>1992687278</match_id> <spectators>3</spectators> <series_id>0</series_id> <game_number>0</game_number> <league_id>3706</league_id> <stream_delay_s>300.000000</stream_delay_s> <radiant_series_wins>0</radiant_series_wins> </result>

This my xml file I want to extract data under Players tag into one table 2) data under Scorecard with MatchID
and 3 Rest of the items into one table . here one game contains Many players and score card . The fieds like lobby_id,match_id,game_number etc will one . Please help me I dont have much experice in xml

4

that's a good start! Now, please post your table definitions as CREATE TABLE statements

5

Gbritton thanks for your reply . I would like to create table from Select * into. Could you please tell how to select data

i have try this but there is no result

DECLARE @XML XML
set @XML= (SELECT CONVERT(XML, BulkColumn,2) AS BulkColumn
FROM OPENROWSET(BULK 'D:\TestTest.xml', SINGLE_BLOB)as X)

select

d.c.value('(/Result/games/game/lobby_id)[0]', 'int') as UserCode--,

from
@xml.nodes('/Result/games/game') d(c) -- the parent node

6

Started to work on this but your sample XML is not valid. e.g.

the tags and is not closed

7

your tag is not called Result. tags are case sensitive

8

Thank you for your quick response .But Now all the row shows NULL

9

Try this in your select:

c.value('lobby_id[1]', 'bigint') as UserCode--,

10

Its working thank you..It takes 2 sec execute. Is there any method to get result better