-- load file into xml variable

DECLARE @x xml

SELECT @x = CAST(c1 as xml)

FROM OPENROWSET(BULK 'D:\temp\sampledata.xml', SINGLE_CLOB) t1(c1)

--turn xml into table with nodes() function:

declare @xml as xml = '

<ArrayOfCustomers

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

>

<Customer id="1">

<CustomerName>Mr Smith</CustomerName>

<Children>

<Child ChildName="Peter" />

<Child ChildName="Ivan" />

</Children>

</Customer>

<Customer id="2">

<CustomerName>Mr Bloggs</CustomerName>

<Children>

<Child ChildName="John" />

</Children>

</Customer>

</ArrayOfCustomers>

'

SELECT AOC.Cust.value('.','varchar(max)')

FROM @xml.nodes('/ArrayOfCustomers/Customer') AS AOC(Cust)

HTH,

Vedran