CODEWITHSUNDEEP
sql-serverxmlxpath
RKN
RKN

Reputation: 962

XPath read values from sql table column

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

Upvotes: 4

Views: 3728

Answers (5)

sandeep rawat
sandeep rawat

Reputation: 4957

Use below query .

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

Upvotes: 0

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

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)

Upvotes: 0

GarethD
GarethD

Reputation: 69819

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;

Upvotes: 3

Devart
Devart

Reputation: 122042

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

Upvotes: 0

har07
har07

Reputation: 89325

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

Upvotes: 0

Related Questions