CODEWITHSUNDEEP
sql-serveropenxml
Ashish Gupta
Ashish Gupta

Reputation: 15139

What is wrong with this OpenXML?

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?

Upvotes: 3

Views: 260

Answers (2)

marc_s
marc_s

Reputation: 754348

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)

Upvotes: 2

Chris W
Chris W

Reputation: 3314

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.

Upvotes: 2

Related Questions