XPath从sql表列读取值

I have below xml present as a column(Name UserBody) value of the table called tblUsers.

我有以下xml作为名为tblUsers的表的列（Name UserBody）值。

I have to read NewUserType name i.e. "SampleUserName" using Xpath in SQL.

我必须在SQL中使用Xpath读取NewUserType名称，即“SampleUserName”。

<UserTypeAdded xmlns="http://schemas.datacontract.org/2004/07/ABC.Domain.Contract.EventModel" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <UserTypeTypeDetails xmlns:a="http://schemas.datacontract.org/2004/07/ABC.Common.Contract">
            <a:Id>550d9a76-3d7d-49f6-9243-f0473d32b123</a:Id>
            <a:Name>Special User Types</a:Name>
        </UserTypeTypeDetails>
        <NewUserType xmlns:a="http://schemas.datacontract.org/2004/07/ABC.Domain.Contract.Base">
            <Id xmlns="http://schemas.datacontract.org/2004/07/ABC.Common.Contract">dfa090ff-9756-42fd-be9b-02ac8c6b123</Id>
            <Name xmlns="http://schemas.datacontract.org/2004/07/ABC.Common.Contract">SampleUserName</Name>
        </NewUserType>
    </UserTypeAdded>

I tried using below statement

我尝试使用以下声明

   SELECT 
    [UserBody].value('(/UserTypeAdded/NewUserType/Name[1])', 'nvarchar(max)') as UserName 
    FROM tblUsers

but No luck

但没有运气

5 个解决方案

#1

You have defined your node incorrectly, you have:

您错误地定义了节点，您有：

'(/UserTypeAdded/NewUserType/Name[1])'

You either need to specify the position of each element:

您需要指定每个元素的位置：

'(/UserTypeAdded[1]/NewUserType[1]/Name[1])'

Or wrap the entire path in parenethesis and specify the position for that:

或者在parenethesis中包装整个路径并指定其位置：

'(/UserTypeAdded/NewUserType/Name)[1]'

You also need to define your XML Namespaces:

您还需要定义XML命名空间：

-- SAMPLE DATA
DECLARE @tblUsers TABLE (UserBody XML);
INSERT @Tblusers 
VALUES('<UserTypeAdded xmlns="http://schemas.datacontract.org/2004/07/ABC.Domain.Contract.EventModel" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <UserTypeTypeDetails xmlns:a="http://schemas.datacontract.org/2004/07/ABC.Common.Contract">
            <a:Id>550d9a76-3d7d-49f6-9243-f0473d32b123</a:Id>
            <a:Name>Special User Types</a:Name>
        </UserTypeTypeDetails>
        <NewUserType xmlns:a="http://schemas.datacontract.org/2004/07/ABC.Domain.Contract.Base">
            <Id xmlns="http://schemas.datacontract.org/2004/07/ABC.Common.Contract">dfa090ff-9756-42fd-be9b-02ac8c6b123</Id>
            <Name xmlns="http://schemas.datacontract.org/2004/07/ABC.Common.Contract">SampleUserName</Name>
        </NewUserType>
    </UserTypeAdded>');

-- QUERY
WITH XMLNAMESPACES
(   'http://schemas.datacontract.org/2004/07/ABC.Domain.Contract.Base' AS a,
    'http://schemas.datacontract.org/2004/07/ABC.Common.Contract' AS x,
    DEFAULT 'http://schemas.datacontract.org/2004/07/ABC.Domain.Contract.EventModel'
)
SELECT *,
        UserBody.value('(/UserTypeAdded/NewUserType/x:Name)[1]', 'nvarchar(max)') as UserName
FROM @TblUsers;

You can also use a wild card for namespaces:

您还可以使用通配符作为名称空间：

SELECT *,
        UserBody.value('(/*:UserTypeAdded/*:NewUserType/*:Name)[1]', 'nvarchar(max)') as UserName
FROM @TblUsers;

#2

Your XML is full of different namespaces which you need to consider in your XQuery. You can use WITH XMLNAMESPACES to map prefix to namespace URI, and use the prefix to reference element in namespace, for example :

您的XML中充满了不同的命名空间，您需要在XQuery中考虑这些命名空间。您可以使用WITH XMLNAMESPACES将前缀映射到名称空间URI，并使用前缀来引用名称空间中的元素，例如：

WITH XMLNAMESPACES 
(
    'http://schemas.datacontract.org/2004/07/ABC.Domain.Contract.EventModel' as utaNs,
    'http://schemas.datacontract.org/2004/07/ABC.Common.Contract' as nameNs
)
SELECT 
    [UserBody].value('(/utaNs:UserTypeAdded/utaNs:NewUserType/nameNs:Name)[1]', 'nvarchar(max)') as UserName 
FROM tblUsers

#3

SELECT @x.value('(/*:UserTypeAdded/*:NewUserType/*:Name)[1]', 'NVARCHAR(MAX)')

#4

Try this -

尝试这个 -

declare @XML xml = '
<UserTypeAdded xmlns="http://schemas.datacontract.org/2004/07/ABC.Domain.Contract.EventModel" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <UserTypeTypeDetails xmlns:a="http://schemas.datacontract.org/2004/07/ABC.Common.Contract">
            <a:Id>550d9a76-3d7d-49f6-9243-f0473d32b123</a:Id>
            <a:Name>Special User Types</a:Name>
        </UserTypeTypeDetails>
        <NewUserType xmlns:a="http://schemas.datacontract.org/2004/07/ABC.Domain.Contract.Base">
            <Id xmlns="http://schemas.datacontract.org/2004/07/ABC.Common.Contract">dfa090ff-9756-42fd-be9b-02ac8c6b123</Id>
            <Name xmlns="http://schemas.datacontract.org/2004/07/ABC.Common.Contract">SampleUserName</Name>
        </NewUserType>
    </UserTypeAdded>'


select T.N.value('(/*:UserTypeAdded/*:NewUserType/*:Name)[1]', 'nvarchar(max)') as UserName 
from @XML.nodes('/*:UserTypeAdded') as T(N)

#5

Use below query .

使用以下查询。

     SELECT  [UserBody].value('(/UserTypeAdded/NewUserType/Name)[1]', 'nvarchar(max)') as UserName 
    FROM tblUsers

#1