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 am executing is this:-

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

I see NULL getting inserted in the table. Could anyone point out the mistake?

Answer 1

By default the OPENXML will look at attribute values or child elements for the data. If you write your select as:

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

It should work ok. Note the addition of 'text()' in to the schema mapping to specify that we just want the text value of the node instead of any attribute value.

Answer 2

Why even bother with the clunky OpenXML stuff?? Just use the basic XQuery support in SQL Server to do this much more elegantly:

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)