xml parsing with xquery returning null records

[Dan]

I've recently begun receiving xml files and ran into a small problem
that seems syntax related, but I simply can't find where it is. Below
is the xml file and sample code of what I'm doing. I cut up the xml
data, it's far more complex then what I'm presenting below. I think
it has something to do what how I'm address eventId but am struggling
with figuring it out. My preference is to stay out of SSIS if at all
possible on this project.

I'm getting rows to return, but they are all null when there is data
in the file.

If you see something with what I'm doing (and replied), I'd be greatly
thankful.

<?xml version="1.0" encoding="UTF-8"?>
<pitchMediaList>
<pitchMedia eventId="243">
<media type="FLASH" url="xx.html"/>
</pitchMedia>
</pitchMediaList>

select x.value('eventId[1]/eventId[1]','int') as url
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK 'c:\videofile.xml',
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes('pitchMediaList/pitchMedia') AS X(pitchMedia);

[Erland Sommarskog]

Dan (dqu...@gmail.com) writes:
><?xml version="1.0" encoding="UTF-8"?>
><pitchMediaList>
> <pitchMedia eventId="243">
> <media type="FLASH" url="xx.html"/>
> </pitchMedia>
></pitchMediaList>
>
>
> select x.value('eventId[1]/eventId[1]','int') as url
> FROM (
> SELECT CAST(x AS XML)
> FROM OPENROWSET(
> BULK 'c:\videofile.xml',
> SINGLE_BLOB) AS T(x)
> ) AS T(x)
> CROSS APPLY x.nodes('pitchMediaList/pitchMedia') AS X(pitchMedia);

When you retrieve attribute, you need to put an @ in front:

DECLARE @x xml
SELECT @x = '<?xml version="1.0" encoding="UTF-8"?>

<pitchMediaList>
<pitchMedia eventId="243">
<media type="FLASH" url="xx.html"/>
</pitchMedia>
</pitchMediaList>'

select X.c.value('@eventId[1]','int') as url
FROM @x.nodes('pitchMediaList/pitchMedia') AS X(c);

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx