XML to SQL Issue

Question

I am trying to parse a sample xml , but I am not getting expected output , I am using openxml to do this :

This is the code :

    declare @myxml xml =
'<Departments>
   <Department>
    <Employees>
      <Employee user="J" id="J10" method="email" date="06/13/2018 08:59">

      </Employee>
      <Employee user="R" id="R10" method="email1" date="07/13/2018 08:59">

      </Employee>
    </Employees>
  </Department>

  <Department>
    <Employees>
      <Employee user="Jason" id="J101" method="email" date="06/13/2018 08:59">

      </Employee>
      <Employee user="Roy" id="R101" method="email1" date="07/13/2018 08:59">

      </Employee>
    </Employees>
  </Department>
</Departments>'
declare @i int =2;
declare @x_path varchar(5000) = (select  'Departments/Department[' + cast(@i as varchar) + ']' )
DECLARE @hDoc AS INT, @SQL NVARCHAR (MAX)
EXEC sp_xml_preparedocument @hDoc OUTPUT, @myxml
SELECT name,id,method,user_date
FROM OPENXML(@hDoc, @x_path)
WITH 
(
name [varchar](1000) 'Employees/Employee/@user',
id [varchar](1000) 'Employees/Employee/@id',
method [varchar](1000) 'Employees/Employee/@method',
user_date [varchar](1000) 'Employee/Employee/@date'
)
EXEC sp_xml_removedocument @hDoc
go

I am getting only 1 row , but I want 2 rows :

Output of above query:

name    id  method  user_date
Jason   J101    email   NULL

Expected Output :

name    id     method   user_date
Jason   J101    email   06/13/2018 08:59
Roy     R101    email   07/13/2018 08:59

Note

I want to iterate only through the second department, that is why I have appended [@i] in the path to make sure that it iterates over 2nd department only. and the value of i would be decided dynamically, as of now I have set it to 2.

Any help would be appreciated. Thanks

Answer 1

One more way:

declare @i int = 2

SELECT  t.c.value('@user', 'nvarchar(10)') as [user],
        t.c.value('@id', 'nvarchar(10)') as id,
        t.c.value('@method', 'nvarchar(10)') as method,
        t.c.value('@date', 'nvarchar(10)') as [date]
FROM @myxml.nodes('/Departments/Department/Employees/Employee') as t(c)
WHERE t.c.value('for $i in . return count(/Departments/Department[. << $i]) ', 'int') = @i

Output:

user       id         method     date
---------- ---------- ---------- ----------
Jason      J101       email      06/13/2018
Roy        R101       email1     07/13/2018

(2 rows affected)