Query to return Xml column data as relational table - how?

Greetings,

I've just begun storing Xml in a SQL Server Xml column. I've got a column that contains something like this 3 element example:

<document xmlns="http://www.lotus.com/dxl" version="7.0" maintenanceversion="2.0" replicaid="852571B800111CE3" form="data">

<item name="OriginalModTime">

<datetime dst="true">20060813T135156,51-05</datetime>

</item>

<item name="Genius_Status_1">

<text>1</text>

</item>

<item name="archive_date">

<datetime>20040331</datetime>

</item>

</document>

I need to write a query that will return a combination of attribute and data values at different "levels". This is what I must achieve as query results:

Name Type Value

- - --

OriginalModTime datetime 20060813T135156,51-05

Genius_Status_1 text 1

archive_date datetime 20040331

Each resultset row must correspond to one "item" element.

Can someone give me an idea of what this query should look like? I'm trying to puzzle my way through xquery...

Thanks,

BCB

I've got everything but the "Type" column figured out. This query returns "Name" and "Value". Can someone suggest the missing logic to return the "Type" value?

Thanks... BCB

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') as [Notes Field],

Item.value('.', 'NVARCHAR(MAX)') as Value

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

|||

This is the working query:

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') AS [Notes Field Name],

Item.value('local-name(./*[1])', 'NVARCHAR(256)') AS [Data Type],

Item.value('.', 'NVARCHAR(MAX)') AS [Field Value]

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

sql