[See stored procedure at bottom]

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[RowNum] INT NOT NULL,

[id] INT NULL,

[parentid] INT NULL,

[nodepath] NVARCHAR(250) NULL,

[nodetype] NVARCHAR(250) NULL,

[nodename] NVARCHAR(250) NULL,

[property] NVARCHAR(250) NULL,

[value] NVARCHAR(250) NULL,

[nodecontents] NVARCHAR(250) NULL,

PRIMARY KEY ([RowNum]))

INSERT INTO #TempTable

EXEC dbo.ParseXMLtoTable

'<countryData>

<Country>

<CName>AMERICAN SAMOA</CName>

<CCode>AS</CCode>

</Country>

<Country>

<CName>ANDORRA</CName>

<CCode>AD</CCode>

</Country>

<Country>

<CName>ANGOLA</CName>

<CCode>AO</CCode>

</Country>

</countryData>'

,'countryData'

SELECT

ROW_NUMBER() OVER (ORDER BY tt.nodecontents) AS ID

,tt.nodecontents AS CName

,tt2.nodecontents AS CCode

FROM

#TempTable AS tt

INNER JOIN #TempTable AS tt2

ON tt.RowNum + 1 = tt2.RowNum

WHERE

tt.nodename = 'CName'

--ID CName CCode

--1 AMERICAN SAMOA AS

--2 ANDORRA AD

--3 ANGOLA AO

IF OBJECT_ID('tempdb..#TempTable2') IS NOT NULL

DROP TABLE #TempTable2

CREATE TABLE #TempTable2 (

[RowNum] INT NOT NULL,

[id] INT NULL,

[parentid] INT NULL,

[nodepath] NVARCHAR(250) NULL,

[nodetype] NVARCHAR(250) NULL,

[nodename] NVARCHAR(250) NULL,

[property] NVARCHAR(250) NULL,

[value] NVARCHAR(250) NULL,

[nodecontents] NVARCHAR(250) NULL,

PRIMARY KEY ([RowNum]))

INSERT INTO #TempTable2

EXEC dbo.ParseXMLtoTable

'<ROOT>

<Customers>

<Customer CustomerID="C001" CustomerName="Arshad Ali">

<Orders>

<Order OrderID="10248" OrderDate="2012-07-04T00:00:00">

<OrderDetail ProductID="10" Quantity="5" />

<OrderDetail ProductID="11" Quantity="12" />

<OrderDetail ProductID="42" Quantity="10" />

</Order>

</Orders>

<Address> Address line 1, 2, 3</Address>

</Customer>

<Customer CustomerID="C002" CustomerName="Paul Henriot">

<Orders>

<Order OrderID="10245" OrderDate="2011-07-04T00:00:00">

<OrderDetail ProductID="11" Quantity="12" />

<OrderDetail ProductID="42" Quantity="10" />

</Order>

</Orders>

<Address> Address line 5, 6, 7</Address>

</Customer>

<Customer CustomerID="C003" CustomerName="Carlos Gonzlez">

<Orders>

<Order OrderID="10283" OrderDate="2012-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3" />

</Order>

</Orders>

<Address> Address line 1, 4, 5</Address>

</Customer>

</Customers>

</ROOT>'

,'ROOT'

SELECT * FROM #TempTable2

-- See http://www.sqlservercentral.com/scripts/XML/100546/[/url]

CREATE PROCEDURE dbo.ParseXMLtoTable

@strXML AS XML

,@rootnode NVARCHAR(255)

AS

BEGIN

SET NOCOUNT ON

DECLARE

@strText AS NVARCHAR(MAX)

,@idoc INT

,@id INT

,@parentid INT

IF OBJECT_ID('tempdb..#ChildList') IS NOT NULL

DROP TABLE #ChildList

CREATE TABLE #ChildList (

[RowNum] INT IDENTITY(1,1) NOT NULL,

[parentid] INT NULL,

[id] INT NULL,

PRIMARY KEY (RowNum))

IF OBJECT_ID('tempdb..#NodeList') IS NOT NULL

DROP TABLE #NodeList

CREATE TABLE #NodeList (

[RowNum] INT NOT NULL,

[id] INT NULL,

[parentid] INT NULL,

[nodetype] INT NULL,

[localname] NVARCHAR(MAX) NULL,

[text] NVARCHAR(MAX) NULL,

PRIMARY KEY (RowNum))

SET @id = 1

SET @parentid = NULL

/* Get rid of tabs and extra spaces */

SET @strText = CAST(@strXML AS NVARCHAR(MAX))

SET @strText =

REPLACE(

REPLACE(

REPLACE(

REPLACE(

@strText

,' ',' '+CHAR(7))

,CHAR(7)+' ','')

,CHAR(7),'')

,CHAR(9),' ')

SET @strXML = CONVERT(XML,@strText)

/* Validate the XML */

EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML

/* Parse the XML data */

;WITH cteChildren (parentid, id)

AS (

SELECT

CAST(p1.parentid AS INT) AS parentid

,CAST(p1.id AS INT) AS id

FROM

OPENXML (@idoc,@rootnode,2) AS p1

UNION ALL

SELECT

CAST(p2.parentid AS INT) AS parentid

,CAST(p2.id AS INT) AS id

FROM

OPENXML (@idoc,@rootnode,2) AS p2

INNER JOIN

cteChildren AS cte

ON cte.id = p2.ParentID

WHERE

p2.parentid = @parentid

)

INSERT INTO #ChildList

SELECT

parentid

,id

FROM cteChildren

INSERT INTO #NodeList

SELECT

#ChildList.RowNum

,xmllist.id

,xmllist.parentid

,xmllist.nodetype

,xmllist.localname

,CAST(xmllist.[text] AS NVARCHAR(MAX)) AS [text]

FROM #ChildList

INNER JOIN

OPENXML (@idoc,@rootnode,2) AS xmllist

ON #ChildList.id = xmllist.id

WHERE

#ChildList.RowNum > 0

/* Display the results */

IF OBJECT_ID('dbo.XML_Nodes') IS NOT NULL

DROP TABLE dbo.XML_Nodes

;WITH RecursiveNodes(RowNum,id,parentid,nodepath,localname,[text],nodetype)

AS (

SELECT

#NodeList.RowNum

,#NodeList.id

,#NodeList.parentid

,CAST('/' + REPLACE(REPLACE(REPLACE(REPLACE(#NodeList.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath

,#NodeList.localname

,CAST(#NodeList.[text] AS NVARCHAR(MAX)) AS [text]

,0 AS nodetype

FROM #ChildList

INNER JOIN

#NodeList

ON #ChildList.id = #NodeList.id

WHERE

#NodeList.parentid IS NULL

AND #ChildList.RowNum > 0

AND #NodeList.RowNum > 0

UNION ALL

SELECT

n.RowNum

,n.id

,n.parentid

,CAST(r.nodepath + '/'+ REPLACE(REPLACE(REPLACE(REPLACE(n.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath

,n.localname

,n.[text]

,n.nodetype

FROM #NodeList AS n

INNER JOIN

RecursiveNodes AS r

ON n.parentid = r.id

WHERE

n.RowNum > 0

AND r.RowNum > 0

AND n.parentid >= 0

)

SELECT

ROW_NUMBER() OVER (ORDER BY Result.RowNum) AS RowNum

,Result.id

,Result.parentid

,Result.nodepath

,Result.nodetype

,Result.nodename

,Result.property

,Result.value

,Result.nodecontents

INTO dbo.XML_Nodes

FROM

(

SELECT

rn.RowNum

,rn.id

,rn.parentid

,rn.nodepath

,(CASE

WHEN rn.nodetype = 0 THEN 'Root'

WHEN rn.nodetype = 1 THEN 'Node'

WHEN rn.nodetype = 2 THEN 'Property'

ELSE 'Data'

END) AS nodetype

,(CASE

WHEN rn.nodetype = 0 THEN rn.localname

WHEN rn.nodetype = 1 THEN rn.localname

WHEN rn.nodetype = 2 THEN (SELECT TOP(1) localname FROM RecursiveNodes WHERE id = rn.parentid)

ELSE NULL

END) AS nodename

,(CASE

WHEN rn.nodetype = 2 THEN rn.localname

ELSE NULL

END) AS property

,(CASE

WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)

ELSE NULL

END) AS value

,(CASE

WHEN rn.nodetype = 1 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)

WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.parentid and [text] is not null)

ELSE NULL

END) AS nodecontents

FROM

RecursiveNodes AS rn

WHERE

rn.localname <> '#text'

) AS Result

WHERE

Result.id >= 0

AND (Result.id = 0

OR property IS NOT NULL

OR value IS NOT NULL

OR nodecontents IS NOT NULL)

SELECT

RowNum

,id

,parentid

,nodepath

,nodetype

,nodename

,property

,value

,nodecontents

FROM

dbo.XML_Nodes

END

GO