[SOLVED] Reading child XML node from column

Reading child XML node from column

I have been tasked with data out of an inventory system that relies on a SQL Server database. One of the columns uses XML to store the Manufacturer and Model information, amongst other things. I have been able to get some of the data extracted, but since XML has one part repeated ("Field") and then more narrowing that occurs after that, I have been unsuccessful.

The XML in the MARCData column looks like this:

<asset>
    <field>
        <name>
            <![CDATA[Instructional Classification]]>
        </name>
        <dataType></dataType>
        <fieldID>34</fieldID>
        <data>
            <![CDATA[]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Bin Location]]>
        </name>
        <dataType>Text</dataType>
        <fieldID>51</fieldID>
        <data>
            <![CDATA[]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Description]]>
        </name>
        <dataType>Text</dataType>
        <fieldID>1</fieldID>
        <data>
            <![CDATA[Laptop]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Replacement Price]]>
        </name>
        <dataType>Currency</dataType>
        <fieldID>24</fieldID>
        <data>
            <![CDATA[]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Manufacturer]]>
        </name>
        <dataType>Text</dataType>
        <fieldID>202</fieldID>
        <data>
            <![CDATA[Lenovo]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Model]]>
        </name>
        <dataType>Text</dataType>
        <fieldID>203</fieldID>
        <data>
            <![CDATA[ThinkPad P17 Gen 2]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Connections]]>
        </name>
        <dataType>Text</dataType>
        <fieldID>1212</fieldID>
        <data>
            <![CDATA[Model Test']]>
        </data>
    </field>
</asset>

The code I have made so far:

SELECT 
    (CAST(MARCData AS xml)).value('(/asset/field)[6]', 'varchar(255)')

I am getting the "sixth" entry of "field" but would like to keep limiting the result down to just get the Model information by itself.

What's listed above returns:

ModelText203ThinkPad P17 Gen 2

But I need it to only return:

ThinkPad P17 Gen 2

I have tried to add more limiting items after the [6], but just get syntax errors in T-SQL:

(CAST(MARCData AS xml)).value('(/asset/field)[6](/name/data)', 'varchar(255)')

I know how to "drill down" into the XML, and I know how to get the sixth item with a certain tag Field[6], but how do you continue "drilling down" after getting the sixth item? (Sorry if phrasing is wrong)

Solution

It seems you just need to add the rest of the path, /data[1] e.g.

SELECT 
    (CAST(MARCData AS xml)).value('(/asset/field)[6]/data[1]', 'varchar(255)')