Xml Query

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

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

<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

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

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

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

the tags and is not closed

your tag is not called Result. tags are case sensitive

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

Try this in your select:

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

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