SQL Server XML Parsing Null Result

Question

Hello I have procedure like below

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AdminTestSaveAnswer]
    @refQuestion xml
AS
BEGIN

    SET NOCOUNT ON;
    SELECT  
       Tbl.Col.value('@Id', 'int'),  
       Tbl.Col.value('@Question', 'varchar(max)'),  
       Tbl.Col.value('@CorrectAnswer', 'varchar(7)'),
       Tbl.Col.value('@Desc', 'varchar(max)')
    FROM   @refQuestion.nodes('//Root') Tbl(Col)

END

I run code like

EXEC [dbo].[SP_AdminTestSaveAnswer] '<Root>
  <Id>2</Id>
  <Question>(I) Yaşayan sanatçılara televizyonlarda pek yer verilmiyor. (II) Bunların içerisinde sürekli olarak ekranda olan tek sanatçı neredeyse Attila İlhan. (III) Demek ki ülkemizde edebiyatçılara, şairlere, romancılara, öykü ve deneme ustalarına ihtiyaç duyulmuyor artık. (III) Şahsen ben 1996’dan beri televizyonlarda anımsanmıyor, oralara çağrılmıyorum. (V) Diğer pek çok yazarın da akla geldiğini, zaman zaman da olsa ekrana davet edildiklerini sanmıyorum.
&lt;/br&gt;&lt;span&gt;&lt;b&gt;
Bu parçadaki numaralanmış cümlelerin hangisinde bir çıkarım söz konusudur?&lt;/b&gt;&lt;/span&gt;</Question>
  <CorrectAnswer>A</CorrectAnswer>
  <Desc>deded</Desc>
  <refTest>1</refTest>
  <Answers>
    <Id>2</Id>
    <Answer>I.</Answer>
    <Code>A</Code>
    <refQuestion>0</refQuestion>
  </Answers>
  <Answers>
    <Id>3</Id>
    <Answer>II.</Answer>
    <Code>B</Code>
    <refQuestion>0</refQuestion>
  </Answers>
  <Answers>
    <Id>4</Id>
    <Answer>III.</Answer>
    <Code>C</Code>
    <refQuestion>0</refQuestion>
  </Answers>
  <Answers>
    <Id>5</Id>
    <Answer>IV.</Answer>
    <Code>D</Code>
    <refQuestion>0</refQuestion>
  </Answers>
  <Answers>
    <Id>6</Id>
    <Answer>V.</Answer>
    <Code>E</Code>
    <refQuestion>0</refQuestion>
  </Answers>
</Root>'

As result I get one row but all of columns are null. Where is my missing ?

Thanks in advance

FYI the sp_ prefix is reserved by Microsoft and should not be used for User Procedures. Doing so has multiple implications, including performance and reliability. — Thom A
– Thom A ♦, Commented Aug 22, 2020 at 21:41
As for why, @Id would reference the property Id not the Node; you want (/Id/text())[1]. Also //Root should be /Root — Thom A
– Thom A ♦, Commented Aug 22, 2020 at 21:45

David Browne - Microsoft · Accepted Answer · 2020-08-22 22:19:50Z

2

@ is for xml attrubites, your data is in elements. So should be something like

SELECT  
   Tbl.Col.value('Id[1]', 'int'),  
   Tbl.Col.value('Question[1]', 'varchar(max)'),  
   Tbl.Col.value('CorrectAnswer[1]', 'varchar(7)'),
   Tbl.Col.value('Desc[1]', 'varchar(max)')
FROM   @refQuestion.nodes('/Root') Tbl(Col)

edited Aug 22, 2020 at 22:19

answered Aug 22, 2020 at 21:57

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

SQL Server XML Parsing Null Result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related