Xml file node reading and inserting as seperate rows in a table

Question

I am new bee in learning microsoft technologies. I struck with an issue in sql server where I need your help.

Well, I have a XML file with below format, please see it for your reference

<Permissions><Denied><User/><Roles/><Groups/></Denied><Allowed><Users/><Roles>admin,user,reader,writer,</Roles><Groups/></Allowed></Permissions>

In which I need to read Roles node values and insert those comma separated values as single row where I I will pass permissionid as a parameter in stored procedure.

here is the table columns (I need to insert single role for single row in test table based on transitionid)

    create table test
    (
      empid int identity(1,1),
      roles varchar(40),
      transitionid int
    )

score 0 · Answer 1 · edited May 23 '17 at 12:17

You have two problems here: getting the data from the XML and splitting it.

If you're using SQL 2016 you're in luck - there's a new STRING_SPLIT function. You could use that like so:

declare @xml xml = '<Permissions><Denied><User/><Roles/><Groups/></Denied><Allowed><Users/><Roles>admin,user,reader,writer,</Roles><Groups/></Allowed></Permissions>';

declare @test table
(
    empid int identity(1,1),
    roles varchar(40),
    transitionid int
)

INSERT @test (roles)
select b.value
FROM @xml.nodes('//Roles/text()')x(csv)
CROSS APPLY STRING_SPLIT(CAST(x.csv.query('.') AS VARCHAR(MAX)), ',')b
where b.value <> ''

select * from @test

Otherwise, you'll have to do something similar using a custom string splitting method, which you can find more about How do I split a string so I can access item x? or https://sqlperformance.com/2012/07/t-sql-queries/split-strings - basically, both require either writing a custom T-SQL function or CLR code that is imported into SQL Server. You could then use the same method as above (replacing STRING_SPLIT with the name of your custom string splitting function).

Xml file node reading and inserting as seperate rows in a table

1 Answers1