title: How can I loop through all parent and child elements in an XML field using SQL
body: <p>I have the below script which is in a semi-working state. I have been able to successfully retrieve all the parent elements, but I want to cursor through those to get all their children. When I try passing the current parent name to the cursor I get an error stating the value needs to be a literal. There must be a way around this error and I am wondering if it is as simple as an issue with my syntax below. Any guidance would be appreciated :) </p>
  
  <pre><code>CREATE TABLE #Parents
  (Name varchar(100))
  
  INSERT INTO #Parents (Name)
  select distinct a.value('fn:local-name(.)', 'nvarchar(50)') as t
  FROM HE_EXTRACT_HISTORY
  CROSS APPLY REQUEST_XML.nodes('//*:SignUp/*') AS xx(a)
  WHERE ID = 1113
  
  --Cursor through each node and get all child elements
  
  DECLARE @parentName VARCHAR(150)
  
  DECLARE db_cursor CURSOR FOR 
  select * from #parents
  
  OPEN db_cursor  
  FETCH NEXT FROM db_cursor INTO @parentName  
  
  WHILE @@FETCH_STATUS = 0  
  BEGIN  
  
          -- Get Child Elements
          select distinct a.value('fn:local-name(.)', 'nvarchar(50)') as t
          FROM HE_EXTRACT_HISTORY
          CROSS APPLY REQUEST_XML.nodes('//*:SignUp/@parentName = sql:variable("@parentName")/*') AS xx(a)
          WHERE ID = 1113
          print @parentName
  
        FETCH NEXT FROM db_cursor INTO @parentName 
  END 
  
  CLOSE db_cursor  
  DEALLOCATE db_cursor 
  </code></pre>