XPath liest Werte aus der SQL-Spalte

Ich habe unter xml als Spalte (Name UserBody) Wert der Tabelle namens tblUsers.

Ich muss den NewUserType-Namen lesen, dh "SampleUserName" mit 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> 

Ich habe versucht, unten zu erklären

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

aber kein Glück

Sie haben Ihren Knoten falsch definiert, Sie haben:

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

Sie müssen entweder die Position jedes Elements angeben:

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

Oder wickle den ganzen path in die Parenethese ein und speziere die Position dafür:

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

Sie müssen auch Ihre XML-Namespaces definieren:

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

Sie können auch eine Wildcard für Namespaces verwenden:

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

Ihr XML ist voll von verschiedenen Namespaces, die Sie in Ihrem XQuery beachten müssen. Sie können WITH XMLNAMESPACES , um Präfix zu Namespace-URI WITH XMLNAMESPACES und das Präfix zum referenceelement im Namespace zu verwenden, zum Beispiel:

 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 
 SELECT @x.value('(/*:UserTypeAdded/*:NewUserType/*:Name)[1]', 'NVARCHAR(MAX)') 

Versuche dies –

 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 TNvalue('(/*:UserTypeAdded/*:NewUserType/*:Name)[1]', 'nvarchar(max)') as UserName from @XML.nodes('/*:UserTypeAdded') as T(N) 

Verwenden Sie die Abfrage.

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