Thread: XML Variable with unknown nodes

I'm passing the following XML to a stored routine on SQL Server 2005:

Code:

<?xml version="1.0" encoding="utf-8"?>
<Item xmlns:xsi="http://www.w3.org/2005/xpath-functions" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <ProductID>...</ProductID>
    <Code>...</Code>
    <Name>...</Name>
    <Description>...</Description>
    <onHand>..</onHand>
    <UnitPrice>..</UnitPrice>
    <Keys>
        <col n="1">..</col>
    </Keys>
    <DataSourceID>..</DataSourceID>
</Item> 

The problem is that, except for the data in bold, I don't know the Elements being passed in. I need a way to query the XML and extract the Element names and have been banging my head against a wall for two days. SQL Server has limited support for XQuery functions and FWLOR.

The above XML would be better passed as:

Code:

<Item>
  <Column ID="ProductID">...</Column>
  <Column ID="Code">...</Column>
:
</Item>

But is the result of doing XMSerialization of a .NET object. I don't believe it is possible to add the ID attribute short of writing a customized Serialization routine (which I may be forced to do).

I'm not an XML whiz yet, but I'm stumped. Is there way to extract the Elements from the <Item> node above (in SQL no less).