sql server 2008 - SQL XML Query Pattern? -

sql server 2008 - SQL XML Query Pattern? -

i have xml below in 141k xml document. can show sql server 2008 xquery insert 2 temp tables, "country" table "state" kid relationship?

thanks.

<?xml version="1.0" encoding="utf-8"?> <countries author="michael john grove" title="country, state-province selections" date="2008-feb-05"> <country name="afghanistan"> <state>badakhshan</state> <state>badghis</state> <state>baghlan</state> </country> <country name="albania"> <state>berat</state> <state>bulqize</state> <state>delvine</state> etc

a version integer identity column primary key.

declare @country table ( countryid int identity primary key, name varchar(50) ) declare @state table ( stateid int identity primary key, countryid int, name varchar(50) ) insert @country (name) select c.c.value('@name', 'varchar(50)') @xml.nodes('/countries/country') c(c) insert @state (countryid, name) select country.countryid, s.s.value('.', 'varchar(50)') @xml.nodes('/countries/country') c(c) cross apply c.c.nodes('state') s(s) inner bring together @country country on country.name = c.c.value('@name', 'varchar(50)')

working sample on se data

and version utilize names primary key.

declare @country table ( countryname varchar(50) primary key ) declare @state table ( statename varchar(50) primary key, countryname varchar(50) ) insert @country (countryname) select distinct c.c.value('@name', 'varchar(50)') @xml.nodes('/countries/country') c(c) insert @state (statename, countryname) select s.s.value('.', 'varchar(50)'), c.c.value('@name', 'varchar(50)') @xml.nodes('/countries/country') c(c) cross apply c.c.nodes('state') s(s)

xml sql-server-2008 tsql xpath xquery