Select data from XML file as table in TSQL

Question

Could someone show me some TSQL to use to query an xml file as if it were a table?

The file is on the server, "C:\xmlfile.xml"

And contains

<ArrayOfSpangemansFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <SpangemansFilter>
        <FilterID>1219</FilterID>
        <Name>Fred</Name>
        <Code>510</Code>
        <Department>N</Department>
        <Number>305327</Number>
    </SpangemansFilter>
    <SpangemansFilter>
        <FilterID>3578</FilterID>
        <Name>Gary</Name>
        <Code>001</Code>
        <Department>B</Department>
        <Number>0692690</Number>
    </SpangemansFilter>
    <SpangemansFilter>
        <FilterID>3579</FilterID>
        <Name>George</Name>
        <Code>001</Code>
        <Department>X</Department>
        <Number>35933</Number>
    </SpangemansFilter>
</ArrayOfSpangemansFilter>

Example output I am after

FilterID    |Name       |Code       |Department             |Number
-------------------------------------------------------------------
1219        |Fred       |510        |N                      |305327
3578        |Gary       |001        |B                      |0692690
3579        |George     |001        |X                      |35933

Answer 1

set @xmlData='<?xml version="1.0"?>
<ArrayOfSpangemansFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SpangemansFilter>
<FilterID>1219</FilterID>
<Name>Fred</Name>
<Code>510</Code>
<Department>N</Department>
<Number>305327</Number>
</SpangemansFilter>
<SpangemansFilter>
<FilterID>3578</FilterID>
<Name>Gary</Name>
<Code>001</Code>
<Department>B</Department>
<Number>0692690</Number>
</SpangemansFilter>
<SpangemansFilter>
<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 )

Produces:

FilterID    Name       Code       Department Number
----------- ---------- ---------- ---------- -----------
1219        Fred       510        N          305327
3578        Gary       001        B          692690
3579        George     001        X          35933

Note: The [1] is needed to indicate that you want to select the first value of the sequence since the query may return more than one matched value per row (imagine your XML containing several FilterIDs per SpangemansFilter).

I thought this was useful to know, so I Googled and read many posts until I found this one.

UPDATE To load from file:

DECLARE @xmlData XML
SET @xmlData = (
  SELECT * FROM OPENROWSET (
    BULK 'C:\yourfile.xml', SINGLE_CLOB
  ) AS xmlData
)

SELECT @xmlData