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?

Duplicate: stackoverflow.com/questions/1302064/…

OMG Ponies
– OMG Ponies

2009-11-16 17:51:19 +00:00
Commented Nov 16, 2009 at 17:51 — OMG Ponies
– OMG Ponies, Commented Nov 16, 2009 at 17:51

Remus Rusanu · Accepted Answer · 2009-11-16 18:24:51Z

4

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.

edited Nov 16, 2009 at 18:24

answered Nov 16, 2009 at 17:46

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marc_s Over a year ago

Probably should be: CROSS APPLY ... nodes('/response/topics/topic') as tab(col);, no? I doubt the /topics/topic XPath expression would select anything useful.....

Remus Rusanu Over a year ago

@marc: In my edit I didn't have the original query so I just typed the from memory. I'll edit to avoid confusion.

Ciaran O'Neill Over a year ago

Wasn't exact solution but you put me on the right path. My xpath needed to be: <code>XmlData.nodes('//response/topics/topic/department/questionHead') AS tab(col)</code>. Also then my select list needed to be similar to (rest left for brevity): <code> SELECT tab.col.value('../../@name', 'varchar(100)') as TopicName, tab.col.value('../@name', 'varchar(100)') as DepartmentName, tab.col.value('@questionID', 'int') as QuestionID, tab.col.value('./question[1]', 'varchar(500)') as QuestionText </code> So essentially I needed to go to the most child node and work my way back

Nestor · Accepted Answer · 2009-11-16 17:46:20Z

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)')

answered Nov 16, 2009 at 17:46

Nestor

14k11 gold badges83 silver badges127 bronze badges

Collectives™ on Stack Overflow

Insert xml data into sql tables

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related