CODEWITHSUNDEEP
sqlsql-serverxml
Harsha W
Harsha W

Reputation: 3366

Inserting multiple values to a table using XML

I need to insert all the RoleTypeIDs(10,9) in the below XML to a table. I followed the below method. It only inserts the 0th indexed XML entry. How to solve this issue?

DECLARE @ResourcePermissionXML xml = '<ResourcePermission><RoleTypeID>10</RoleTypeID><RoleTypeID>9</RoleTypeID></ResourcePermission>'

DECLARE @ResourcePermissionList AS TABLE(RoleTypeID INT);

INSERT INTO @ResourcePermissionList(RoleTypeID)
   SELECT Entries.value('(RoleTypeID)[1]', 'INT') AS RoleTypeID
   FROM @ResourcePermissionXML.nodes('/ResourcePermission') AS PermissionEntries(Entries)

SELECT * FROM @ResourcePermissionList

Upvotes: 1

Views: 186

Answers (1)

marc_s
marc_s

Reputation: 754518

Just change your INSERT statement to this:

INSERT INTO @ResourcePermissionList(RoleTypeID)
   SELECT Entries.value('(.)[1]', 'INT') AS RoleTypeID
   FROM @ResourcePermissionXML.nodes('/ResourcePermission/RoleTypeID') AS PermissionEntries(Entries)

Basically, change the XPath expression in .nodes() so that it returns a list of XML fragements for each <RoleTypeID> XML element - then you can "reach into" that XML element and get its value, and since the XPath will return all <RoleTypeID> elements, you get all values.

Upvotes: 2

Related Questions