XPath read values from sql table column

Question

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

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

<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

GarethD · Accepted Answer · 2016-05-24 13:20:33Z

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:

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

You also need to define your XML Namespaces:

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

har07 · Accepted Answer · 2016-05-24 13:13:03Z

0

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 :

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

answered May 24, 2016 at 13:13

har07

89.5k12 gold badges87 silver badges143 bronze badges

Comments

Devart · Accepted Answer · 2016-05-24 13:16:36Z

0

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

answered May 24, 2016 at 13:16

Devart

120k23 gold badges172 silver badges189 bronze badges

Comments

Krishnraj Rana · Accepted Answer · 2016-05-24 13:21:03Z

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)

sandeep rawat · Accepted Answer · 2016-05-24 13:44:15Z

0

Use below query .

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

answered May 24, 2016 at 13:44

sandeep rawat

4,9571 gold badge22 silver badges40 bronze badges

Collectives™ on Stack Overflow

XPath read values from sql table column

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related