使用TSQL从XML中提取信息

XML below is shaped in an unusual way. How would I extract all the pairs of field names and field values from following XML using XQuery in SQL Server? I am particular interested in extracting field values with a field name of 1006.

下面的XML以不寻常的方式塑造。如何在SQL Server中使用XQuery从以下XML中提取所有字段名称和字段值?我特别感兴趣的是提取字段名称为1006的字段值。

<details>
  <fieldname>1006</fieldname>
  <fieldvalue>HSBC BANK PLC</fieldvalue>
</details>
<details>
  <fieldname>1005</fieldname>
  <fieldvalue>POOLE   LONGFLEET</fieldvalue>
</details>
<details>
  <fieldname>1059</fieldname>
  <fieldvalue>165-167 High Street</fieldvalue>
</details>
<details>
  <fieldname>1063</fieldname>
  <fieldvalue>POOLE</fieldvalue>
</details>

I have a relatively large number of XMLs to process and so far I have been using a combination of patIndex and substring to do this.

我有相对大量的XML要处理,到目前为止我一直在使用patIndex和substring的组合来做到这一点。

1 个解决方案

#1

Try this one-

试试这个 -

DECLARE @XML XML
SELECT @XML = '
<details>
  <fieldname>1006</fieldname>
  <fieldvalue>HSBC BANK PLC</fieldvalue>
</details>
<details>
  <fieldname>1006</fieldname>
  <fieldvalue>test2</fieldvalue>
</details>
<details>
  <fieldname>1005</fieldname>
  <fieldvalue>POOLE   LONGFLEET</fieldvalue>
</details>
<details>
  <fieldname>1059</fieldname>
  <fieldvalue>165-167 High Street</fieldvalue>
</details>
<details>
  <fieldname>1063</fieldname>
  <fieldvalue>POOLE</fieldvalue>
</details>'

SELECT 
      fieldname = t.c.value('./fieldname[1]', 'INT')
    , fieldvalue = t.c.value('./fieldvalue[1]', 'VARCHAR(100)')
FROM @XML.nodes('details') t(c)
WHERE t.c.value('./fieldname[1]', 'INT') = 1006

In output -

在输出 -

Update:

In example, XML don't have a ROOT node, so you just need correctly specify the path:

例如,XML没有ROOT节点,因此您只需要正确指定路径:

SELECT t.c.query('.')
FROM @XML.nodes('details') t(c)

Query split your nodes into rows:

查询将节点拆分为行:

After it, for each row you can get the data via XML method value:

在它之后,对于每一行,您可以通过XML方法值获取数据:

t.c.value('<path>', '<datatype>')

For additional information read this topic.

有关其他信息,请阅读本主题。