title: Insert xml data into sql tables
body: <p>I have an xml document with the following format:</p>
  
  <pre><code>&lt;response username="123" customerName="CustomerName" siteName="SiteName" customerID="123" Period="2009"&gt;
    &lt;topics&gt;
      &lt;topic name="MyTopic"&gt;
        &lt;department name="MyDepartment" parent="OriginalDepartment"&gt;
          &lt;questionHead result="Go" group="Group A" surveyID="1" questionID="2" responseID="3"&gt;
            &lt;question&gt;My Question&lt;/question&gt;
            &lt;answer&gt;My Ansert&lt;/answer&gt;
            &lt;comment&gt;Good Answer&lt;/comment&gt;
            &lt;reference&gt;Page 10&lt;/reference&gt;
          &lt;/questionHead&gt;
          &lt;questionHead result="Go" group="Group A" surveyID="1" questionID="2" responseID="3"&gt;
            &lt;question&gt;My Question&lt;/question&gt;
            &lt;answer&gt;My Ansert&lt;/answer&gt;
            &lt;comment&gt;Good Answer&lt;/comment&gt;
            &lt;reference&gt;Page 10&lt;/reference&gt;
          &lt;/questionHead&gt;
  </code></pre>
  
  <p>...</p>
  
  <p>There are multiple, topics and departments.  I need to insert this data into a sql server table.  My table has this schema:</p>
  
  <pre><code>CREATE TABLE [dbo].[Questions](
      [ImportQuestionID] [int] IDENTITY(1,1) NOT NULL,
      [TopicName] [varchar](100) NULL,
      [DepartmentName] [varchar](100) NULL,
      [ParentDepartmentName] [varchar](100) NULL,
      [QuestionID] [int] NOT NULL,
      [SurveyID] [int] NOT NULL,
      [ResponseID] [int] NOT NULL,
      [PageNumber] [int] NOT NULL,
      [OrderNumber] [int] NOT NULL,
      [Result] [varchar](10) NULL,
      [GroupName] [varchar](100) NULL,
      [QuestionText] [varchar](500) NOT NULL,
      [AnswerText] [varchar](500) NOT NULL,
      [Comment] [varchar](500) NULL,
      [Reference] [varchar](500) NULL)
  </code></pre>
  
  <p>So I've imported my xml file into a temporary table with a xml column and now I'm trying to parse the file and put it into a relational table.</p>
  
  <p>Here's what I'm trying:</p>
  
  <pre><code>INSERT INTO [SRCL_XmlTest].[dbo].[Questions]
             field list...
  SELECT tab.col.value('./topic/@name', 'varchar(100)') as TopicName,
          tab.col.value('./topic/department/@name', 'varchar(100)') as DepartmentName,
          tab.col.value('./topic/department/@parent', 'varchar(100)') as ParentDepartmentName,
          tab.col.value('./topic/department/questionH/@questionID', 'int') as QuestionID,
          tab.col.value('./topic/department/questionH/@surveyID', 'int') as SurveyID,
          tab.col.value('./topic/department/questionH/@responseID', 'int') as ResponseID,
          tab.col.value('./topic/department/questionH/@pageNumber', 'int') as PageNumber,
          tab.col.value('./topic/department/questionH/@orderNumber', 'int') as OrderNumber,
          tab.col.value('./topic/department/questionH/@result', 'varchar(10)') as ResultColourCode,
          tab.col.value('./topic/department/questionH/@group', 'varchar(100)') as GroupName,
          tab.col.value('./topic/department/questionH/question', 'varchar(500)') as QuestionText,
          tab.col.value('./topic/department/questionH/answer', 'varchar(500)') as AnswerText,
          tab.col.value('./topic/department/questionH/comment', 'varchar(500)') as Comment,
          tab.col.value('./topic/department/questionH/reference', 'varchar(500)') as Reference
  FROM FileImport
  CROSS APPLY
  XmlData.nodes('//response/topics') AS tab(col)
  </code></pre>
  
  <p>However, I keep getting the error: XQuery [FileImport.XmlData.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'</p>
  
  <p>This is because there are multiple topic nodes in the xml?  I've tried changing my select to:
  XmlData.nodes('//response/topics/topic/department/questionHead/question') AS tab(col)</p>
  
  <p>and I can now access the question, but can't get anywhere near the answer.  Any one with some ideas?</p>