Insert xml data into sql tables

Question

I have an xml document with the following format:

<response username="123" customerName="CustomerName" siteName="SiteName" customerID="123" Period="2009">
  <topics>
    <topic name="MyTopic">
      <department name="MyDepartment" parent="OriginalDepartment">
        <questionHead result="Go" group="Group A" surveyID="1" questionID="2" responseID="3">
          <question>My Question</question>
          <answer>My Ansert</answer>
          <comment>Good Answer</comment>
          <reference>Page 10</reference>
        </questionHead>
        <questionHead result="Go" group="Group A" surveyID="1" questionID="2" responseID="3">
          <question>My Question</question>
          <answer>My Ansert</answer>
          <comment>Good Answer</comment>
          <reference>Page 10</reference>
        </questionHead>

...

There are multiple, topics and departments. I need to insert this data into a sql server table. My table has this schema:

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)

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.

Here's what I'm trying:

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)

However, I keep getting the error: XQuery [FileImport.XmlData.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

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)

and I can now access the question, but can't get anywhere near the answer. Any one with some ideas?

Answer 1

Try using [1] inside the xpath of your .value() For instance:

tab.col.value('./topic[1]/@name', 'varchar(100)')

instead of just

tab.col.value('./topic/@name', 'varchar(100)')

Answer 2

Put a singleton in every xml.value:

tab.col.value('(./topic)[1]/@name', 'varchar(100)')
tab.col.value('(./topic/department)[1]/@name', 'varchar(100)')
...

Whitouth an XML schema declaration there is no way SQL can guess that topics/topic is a singleton, so you have to explicitly force it with the (...)[1].

Update

If your XML has multiple <topic> elements for a <topics> parent your query is incorrect. You need to move the topic into the nodes:

SELECT
tab.col.value('@name',...)
tab.col.value('(./department)[1]',...)
...
FROM ...
CROSS APPLY ... nodes('//response/topics/topic') as tab(col);

This way you project a row for each topic in a all topics nodes. Your original query can only select one single topic from each topics parent.