XPath从SQL表列中读取值

Question

我有一个XML文档作为表格tblUsers中名为UserBody的列的值。

我需要在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>

我尝试使用以下语句

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

但是没有运气

- Roshan

Answer 1

您的节点定义有误，您的代码如下：

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

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

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

或者将整个路径都放在括号中，并指定其位置：

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

您还需要定义您的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;

您还可以使用通配符来匹配命名空间:

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

- GarethD

Answer 2

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

- har07

Answer 3

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

- Devart

Answer 4

可以试试这个 -

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)

- Krishnraj Rana

Answer 5

请使用以下查询。

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

- sandeep rawat