How to get all xml elements from a specific section of xml column using xpath?

Question

I have two tables tblUser and tblUserPermissions and I need all the permission elements from the below xml column (XmlData) for each user in tblUser. There is only one userid element in each XmlData.

<User xmlns="">
  <userid>user1</userid>
  <permissions>
    <permission>per1</permission>
    <permission>per2</permission>
    <permission>per3</permission>
  </permissions>
  //Other elements here
</User>

I am trying this query.

SELECT u.UserID, UserPermissions.perm.value('permission', 'varchar(50)')
FROM tblUserPermissions up WITH (NOLOCK)
INNER JOIN tblUser u ON u.UserID = up.XmlData.value('(/User/userid)[1]', 'int')
cross apply up.XmlData.nodes('/User/permissions') AS UserPermissions (perm)

How can I get the result as shown below?

UserID | Permission

user1 | per1
user1 | per2
user1 | per3
user2 | per1
user2 | per2
user3 | per1
user3 | per2
user3 | per3

I get an error as below. Any idea what needs to be changed in my query? Thanks for any suggestions!

XQuery [tblVORRequest.RequestXml.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Import XML in a temptable then compare and SELECT it – Amirhossein Mar 06 '20 at 21:06 — Amirhossein, Mar 06 '20 at 21:06

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Copied from this link

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Although there is only one ProductID attribute in the XML instance, the static typing rules require you to explicitly specify that the path expression returns a singleton. Therefore, the additional [1] is specified at the end of the path expression. For more information about static typing, see XQuery and Static Typing.

So to start you off, all you need to do is something like this.

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp (test XML)

INSERT INTO #temp
(
    test
)
VALUES
('<User xmlns="">
  <userid>user1</userid>
  <permissions>
    <permission>per1</permission>
    <permission>per2</permission>
    <permission>per3</permission>
  </permissions>
  //Other elements here
</User>'
    )

SELECT UserPermissions.perm.value('permission[1]', 'varchar(50)')
FROM #temp up
cross apply up.test.nodes('/User/permissions') AS UserPermissions (perm)

How to get all xml elements from a specific section of xml column using xpath?

1 Answers1