Xml To table

Posted on 2011-09-07
Last Modified: 2012-05-12
Hi ,
I have @xml variable, I need to convert to table, I can not grab "Answer Value" from xml
( Name NVARCHAR(50), Title NVARCHAR(50), Value NVARCHAR(50) )

INSERT INTO @tempTable SELECT Tbl.Col.value('@Name', 'NVARCHAR(50)')
          , Tbl.Col.value('@Title', 'NVARCHAR(50)')
          , Tbl.Col.value('@Value', 'NVARCHAR(50)')
            FROM @xml.nodes('//Question') Tbl(Col)

And the xml is below:
    <Question Name="PleaseSpecify" add="canda">
    <Answer Value="" />
  <Question Name="aa" add="usa">
    <Answer Value="" />
Question by:samprg
LVL 59

Accepted Solution

Kevin Cross earned 500 total points
ID: 36499541
You are very close. Just fix the XPath.
DECLARE @tempTable TABLE(Name NVARCHAR(50), Title NVARCHAR(50), Value NVARCHAR(50));
SET @xml = '
  <Question Name="PleaseSpecify" add="canda">
    <Answer Value="Test" />
  <Question Name="aa" add="usa">
    <Answer Value="Example" />

INSERT INTO @tempTable(Name, Title, Value)
SELECT Tbl.Col.value('@Name', 'NVARCHAR(50)')
     , Tbl.Col.value('@Title', 'NVARCHAR(50)')
     , Tbl.Col.value('Answer[1]/@Value', 'NVARCHAR(50)')
FROM @xml.nodes('//Question') Tbl(Col)

SELECT Name, Title, Value
FROM @tempTable

Open in new window


Author Closing Comment

ID: 36499553

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now