Passing parameters in nested XML in SQL Server

Question

I have an xml file like below. The xml query with main branch teacherdetails can be a single object or multiple ones. Inside teacherdetails students count is represented in b and student list is presented as nested object.

<teacherdetails>
    <teacher>2222</teacher>
    <a>10</a>
    <b>3</b>
    <students>
        <student>
          <stua>2000</stua>
          <dista>1</dista>
        </student>
        <student>
          <stua>20</stua>
          <dista>1</dista>
         </student>
         <student>
          <stua>20</stua>
          <dista>1</dista>
         </student>
         <student>
             <Reportno>1586215497241</Reportno>
             <sal>
                <month>13.245555</month>
                <month>72.234355</month>
             </sal>
</teacherdetails>
<teacherdetails>
      <teacher>2222</teacher>
      <a>10</a>
      <b>3</b>
      <students>
        <student>
          <stua>2000</stua>
          <dista>1</dista>
        </student>
        <student>
          <stua>20</stua>
          <dista>1</dista>
         </student>
        <student>
          <stua>20</stua>
          <dista>1</dista>
         </student>
        <student>
      <Reportno>1586215497241</Reportno>
      <sal>
        <month>13.245555</month>
        <month>72.234355</month>
      </sal>
</teacherdetails>

I am trying to access teacher, a, b, student details by using SQL. When I am using the xml.nodes using a while loop, I am trying to set values below which is throwing an error

;with numbers as
(
    select number
    from master..spt_values
    where type = 'P'
)
select
    T.N.value('teacher[1]', 'varchar(50)') as teacher,
    T.N.value('(students/student/stua[position()=sql:column("N.Number")])['+ @set +']', 'varchar(max)') as student,
    T.N.value('(students/student/dista[position()=sql:column("N.Number")])['+ @set +']', 'varchar(max)')as distance
from 
    @string.nodes('/teacherdetails') as T(N)
cross join 
    numbers as n
where 
    n.number between 1 and (T.N.value('count(students)', 'int'))

Note: when I hard code 1 or 2 in the place of @set value it is working fine

Error thrown:

String literal is expected here...

I tried changing quotes making it a dynamic query

score 0 · Answer 1 · answered Apr 10 '20 at 19:36

declare @x xml = N'
<teacherdetails>
  <teacher>2222</teacher>
  <a>10</a>
  <b>3</b>
  <students>
    <student>
      <stua>2000</stua>
      <dista>1</dista>
    </student>
    <student>
      <stua>20</stua>
      <dista>2</dista>
    </student>
    <student>
      <stua>30</stua>
      <dista>3</dista>
    </student>
  </students>
  <Reportno>1586215497241</Reportno>
  <sal>
    <month>13.245555</month>
    <month>72.234355</month>
  </sal>
</teacherdetails>
<teacherdetails>
  <teacher>3456</teacher>
  <a>10</a>
  <b>2</b>
  <students>
    <student>
      <stua>789</stua>
      <dista>1</dista>
    </student>
    <student>
      <stua>20</stua>
      <dista>2</dista>
    </student>
  </students>
  <Reportno>1586215343434</Reportno>
  <sal>
    <month>13.245555</month>
    <month>72.234355</month>
  </sal>
</teacherdetails>
';    


select 
    t.tchr.value('./teacher[1]', 'nvarchar(50)') as teacherId,
    s.std.value('./stua[1]', 'nvarchar(50)') as stua,
    s.std.value('./dista[1]', 'nvarchar(50)') as dista  
from @x.nodes('./teacherdetails') as t(tchr) --teacher
cross apply t.tchr.nodes('./students/student') as s(std); --students of each teacher


--for your approach, student[position()= sql:column("n.number")]/stua is always a single element thus [1]
;with numbers as(
select number
from master..spt_values
where type = 'P'
)
select
n.number,
T.N.value('teacher[1]', 'varchar(50)') as teacher,
T.N.value('(./students/student[position()= sql:column("n.number")]/stua)[1]', 'varchar(max)') as student,
T.N.value('(./students/student[position()= sql:column("n.number")]/dista)[1]', 'varchar(max)')as distance
from @x.nodes('/teacherdetails')  as T(N)
cross join numbers as n
where n.number between 1 and (T.N.value('./b[1]','int'));

Passing parameters in nested XML in SQL Server

1 Answers1