XML类型列的SQL Server中的XPATH查询

I have a table 'TEST', It has a column 'info' of type xml. Column info has XML like

我有一个“测试”表，它有一个xml类型的“info”列。列信息与XML类似

<RootEPS xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <EPS key="FirstName" value="Test" />
   <EPS key="LastName" value="Singh" />
   <EPS key="address1" value="23 t-2" />
   <EPS key="EmailAddress" value="test.singh@gmail.com" />  
</RootEPS>

I want to create a XPATH sql query to get the data of attribute 'value' of each EPS node e.g. firstName as fName, lastName as lName based on given email.

我想创建一个XPATH sql查询来获取每个EPS节点的属性“value”的数据，例如，根据给定的电子邮件，firstName作为fName, lastName作为lName。

For an input email test.singh@gmail.com I want an output like

对于输入电子邮件test.singh@gmail.com，我需要一个类似的输出

fName  |  lName  | address1  |  email
Test   |  Singh  | 23 t-2    | test.singh@gmail.com

I am trying something like this but it's not working

我正在尝试这样的东西，但是没用

SELECT distinct
  N.x.value('@value', 'nvarchar(100)') as fName,
  N.x.value('@value', 'nvarchar(100)') as lName,
  N.x.value('@value', 'nvarchar(100)') as address1,
  N.x.value('@value', 'nvarchar(100)') as email,
FROM
  TEST AS RO
   CROSS APPLY
   RO.info.nodes('/RootEPS/EPS[@key="EmailAddress" and @value="test.singh@gmail.com"]') AS N(x)

In output I am getting only email value. Mainly having issue in N.x.value('@value' part.

在输出中，我只能获得电子邮件的值。主要在N.x有问题。值(“@ value”部分。

1 个解决方案

#1

You're selecting a single EPS node. Use values on the entire RootEPS node and you can query all of its EPS children:

选择一个EPS节点。在整个RootEPS节点上使用值，您可以查询它的所有EPS子节点:

SELECT  info.value('(RootEPS/EPS[@key="FirstName"]/@value)[1]', 'nvarchar(100)') as fName
,       info.value('(RootEPS/EPS[@key="LastName"]/@value)[1]', 'nvarchar(100)') as lName
,       info.value('(RootEPS/EPS[@key="address1"]/@value)[1]', 'nvarchar(100)') as address1
,       info.value('(RootEPS/EPS[@key="EmailAddress"]/@value)[1]', 'nvarchar(100)') 
            as email
FROM    TEST
WHERE   info.value('(RootEPS/EPS[@key="EmailAddress"]/@value)[1]', 'nvarchar(100)')
            = 'test.singh@gmail.com'

Working example at SE Data.

SE数据的工作示例。

#1