在SQL查询中从xml中提取数据

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.

我正在尝试查询SQL 2008R2数据库Surveillance并从表中的XML列设置中提取MacAddress值。硬件我是SQL查询的新手，但很快就学习了。任何建议将不胜感激。

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

1 个解决方案

#1

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

您可以使用函数值（）并更正XQuery以获得所需的结果：

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.

在这里，您可以找到具有值为MacAddress的子元素名称的设置元素，并获取其子元素值的文本。使用value（）函数时，需要提供数据类型，因此在这种情况下它以varchar（255）的形式提供。

Here is a link to SQL Fiddle

这是SQL Fiddle的链接

#1