?
Solved

Xml To table

Posted on 2011-09-07
2
Medium Priority
?
252 Views
Last Modified: 2012-05-12
Hi ,
I have @xml variable, I need to convert to table, I can not grab "Answer Value" from xml
Thanks
DECLARE @tempTable TABLE
( 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:
<Root>
    <Question Name="PleaseSpecify" add="canda">
    <Answer Value="" />
  </Question>
  <Question Name="aa" add="usa">
    <Answer Value="" />
  </Question>
</Root>
0
Comment
Question by:samprg
2 Comments
 
LVL 60

Accepted Solution

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

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

0
 

Author Closing Comment

by:samprg
ID: 36499553
Awesome
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

750 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