Thank you guys very much.

I'm going to add the export and also the import part so I can just look it up in my briefcas when I forget.

Thanks again!

USE AdventureWorks;

DECLARE @XMLdata XML

SELECT @XMLdata =

(SELECT

p.SalesPersonID "SalesPersonID"

, p.TerritoryID "TerritoryID"

, (SELECT TOP 5

s2.name "Name"

, s2.ModifiedDate "ModifiedDate"

FROM

Sales.Store s2

WHERE

s2.SalesPersonID = p.SalesPersonID

FOR XML PATH('Sale'), TYPE) "Sales"

FROM

Sales.SalesPerson p

WHERE

p.SalesPersonID = 275

FOR XML PATH ('SalesPerson'), TYPE)

--GET RECORDSET

SELECT

r.x.value('SalesPersonID[1]','int')

, r.x.value('TerritoryID[1]','int')

, d.x.value('Name[1]','VARCHAR(100)')

, d.x.value('ModifiedDate[1]','VARCHAR(100)')

FROM

@XMLdata.nodes('(/SalesPerson)') AS r(x) OUTER APPLY r.x.nodes('Sales/Sale') AS d(x)

--GET LEVELS

SELECT

t.c.value('SalesPersonID[1]','int')

, t.c.value('TerritoryID[1]','VARCHAR(255)')

FROM

@XMLdata.nodes('/SalesPerson') t(c);

SELECT

d.x.value('Name[1]','VARCHAR(100)')

, d.x.value('ModifiedDate[1]','VARCHAR(100)')

FROM

@XMLdata.nodes('(/SalesPerson)') AS r(x) OUTER APPLY r.x.nodes('Sales/Sale') AS d(x)