[xml] Select data by XML file as table in TSQL

[Costas]

reference
http://stackoverflow.com/a/7650105

at sql managment studio use :

set @xmlData='<?xml version="1.0"?>
<ArrayOfSpangemansFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
[B]
<FilterID>1219</FilterID>
<Name>Fred</Name>
<Code>510</Code>
<Department>N</Department>
<Number>305327</Number>
</SpangemansFilter>
[B]
<FilterID>3578</FilterID>
<Name>Gary</Name>
<Code>001</Code>
<Department>B</Department>
<Number>0692690</Number>
</SpangemansFilter>
[B]
<FilterID>3579</FilterID>
<Name>George</Name>
<Code>001</Code>
<Department>X</Department>
<Number>35933</Number>
</SpangemansFilter>
</ArrayOfSpangemansFilter>'


SELECT 
  ref.value('FilterID[1]', 'int') AS FilterID ,
  ref.value('Name[1]', 'NVARCHAR (10)') AS Name ,
  ref.value('Code[1]', 'NVARCHAR (10)') AS Code ,
  ref.value('Department[1]', 'NVARCHAR (3)') AS Department,
  ref.value('Number[1]', 'int') AS Number      
FROM @xmlData.nodes('/ArrayOfSpangemansFilter/SpangemansFilter') 
xmlData( ref )

alternative solutions
office 2013 > Ribbon-Tab 'Data' > Import from XML (took 5min to import 4mb XML)
http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx
http://www.databasejournal.com/features/mysql/importing-xml-csv-text-and-ms-excel-files-into-mysql.html