I'm trying to query the SQL 2008R2 database Surveillance
and extract the MacAddress
value from this XML Column Settings
in the table Hardware
I'm brand new to SQL queries but quickly learning. Any suggestions would be GREATLY appreciated.
<properties>
<settings hwid="hardware">
<setting typeid="DAF813F8-2E02-4AE5-9F0F-B0FB203D3CB6">
<name>ProductID</name>
<value>AxisP3301</value>
</setting>
<setting typeid="F9D27CE8-8FA9-4B91-8C85-3E80DE7EF0AB">
<name>MacAddress</name>
<value>00408CC3ABCF</value>
</setting>
<setting typeid="FC714851-E998-4738-A140-F4FF20E99DEB">
<name>FirmwareVersion</name>
<value>5.40.9.2</value>
</setting>
<setting typeid="F1DED00E-9614-475F-A6F7-BA29FCA8DE39">
<name>SerialNumber</name>
<value>00408CC3ABCF</value>
</setting>
<setting typeid="5BE128A3-BDEC-4FEE-8690-D575DF03E3EE">
<name>Bandwidth</name>
<value>Unlimited</value>
</setting>
<setting typeid="188A93DE-67D8-417E-BCFC-FF5B3F74434A">
<name>AudioEncoding</name>
<value>G711</value>
</setting>
<setting typeid="5ADCDA37-78B3-4AB9-93E5-966EDE722FA3">
<name>AlertEventServerIP</name>
<value />
</setting>
<setting typeid="922AAF32-821A-4E24-A646-72A4AE592364">
<name>AlertEventServerPort</name>
<value>0</value>
</setting>
<setting typeid="18BF9B14-D675-40A1-B57D-90A03272468A">
<name>SmtpEventServerIP</name>
<value />
</setting>
<setting typeid="5F614912-4C46-453E-9043-3BFC15321E56">
<name>SmtpEventServerPort</name>
<value>25</value>
</setting>
<setting typeid="9F739984-A2C0-4E09-AAB6-97F229E9F208">
<name>InternalEventServerIP</name>
<value>25</value>
</setting>
<setting typeid="59094737-81C6-4FC2-B748-0005C390A775">
<name>AttachmentSavePath</name>
<value />
</setting>
<setting typeid="AF8875BC-7B45-4EBC-92DC-4E3959810A2E">
<name>InternalEventServerPort</name>
<value />
</setting>
<setting typeid="2B22F067-7AA4-4486-A206-85CBE3BE3285">
<name>HTTPSPort</name>
<value>443</value>
</setting>
<setting typeid="DA6E7891-54FA-4902-8164-75A790C11F17">
<name>HTTPSEnabled</name>
<value>no</value>
</setting>
</settings>
</properties>
You can use function
value()
and correct XQuery to get the result you need:Here you find the
setting
element that has child elementname
with valueMacAddress
and take the text of its child elementvalue
. When usingvalue()
function you need to provide the data type, so it is provided asvarchar(255)
in this case.Here is a link to SQL Fiddle