What is wrong with this OpenXML?

Question

ALTER PROCEDURE GetSingersGenere
(@SingerData ntext)
AS
BEGIN
    DECLARE @hDoc int      
    exec sp_xml_preparedocument @hDoc OUTPUT,@SingerData


    IF OBject_id('SingerTable') IS NOT NULL
    BEGIN
        DROP TABLE SingerTable

    END

    CREATE TABLE SingerTable
    (
    SingerName varchar(200)
    )

    INSERT INTO SingerTable 
    (
    SingerName
    )
    SELECT * FROM OpenXML (@hDoc,'/Singers/Singer')
    WITH (SingerName varchar(200)) XMLSinger

    SELECT * FROM SingerTable
END

and the way I do this is: -

EXEC GetSingersGenere
 '<Singers>
<Singer>
Joe
</Singer>
<Singer>
ACDC
</Singer>
</Singers>'

I see that NULL is inserted into the table. Can anyone point out an error?

Answer 1

By default, OPENXML will consider attribute values ​​or children for data. If you write your choice as:

SELECT * FROM OpenXML (@hDoc,'/Singers/Singer')
    WITH (SingerName varchar(200) 'text()') XMLSinger

It should work fine. Note the addition of "text ()" to the schema mapping to indicate that we just want the text value of the node instead of any attribute value.

Answer 2

Why even bother with the awkward OpenXML stuff? Just use the basic XQuery support in SQL Server to make it much more elegant:

ALTER PROCEDURE GetSingersGenre(@SingerData XML)
AS
BEGIN
   INSERT INTO dbo.SingerTable(SingerName)
      SELECT
         Singer.Node.value('(.)[1]', 'varchar(50)')
      FROM
         @SingerData.nodes('/Singers/Singer') AS Singer(Node)