Extracting data from xml in SQL query

Question

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>

Are you only looking at one row of data or are there multiple row with an XML data type you're trying to parse? — jradich1234
– jradich1234, Commented Jun 9, 2015 at 16:48

HingeSight · Accepted Answer · 2015-06-09 17:11:07Z

4

You can use function value() and correct XQuery to get the result you need:

SELECT Settings.value('(/properties/settings/setting[name="MacAddress"]/value/text())[1]', 'varchar(255)') as MacAddress 
FROM Hardware

Here you find the setting element that has child element name with value MacAddress and take the text of its child element value. When using value() function you need to provide the data type, so it is provided as varchar(255) in this case.

Here is a link to SQL Fiddle

edited Jun 9, 2015 at 17:11

HingeSight

4136 silver badges15 bronze badges

answered Jun 9, 2015 at 16:54

krivtom

25k10 gold badges53 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extracting data from xml in SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related