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