[SQL] Reading child XML node from column

I have been tasked with data out of an inventory system that relies on SQL 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:

<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 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)

Continue reading...