CODEWITHSUNDEEP
sql-serverxml
Bokambo
Bokambo

Reputation: 4480

Insert into SQL DB via XML : it inserts null values

I am trying to insert data into a SQL Server database using a stored procedure:

CREATE PROCEDURE [dbo].[InsertUSCCData]
   @xml XML
AS
BEGIN
   SET NOCOUNT ON;

   INSERT INTO [TBL_USCC] (Name, Duration, RowNo,
        Year1, Year2, Year3, Year4, Year5,
        Avg3Year, Avg5Year, CurrentYear,
        CurrentYear1, CurrentYear2, CurrentYear3, CurrentYear4, CurrentYear5,
        Current3YearAvg, Current5YearAvg)
     SELECT
        [Table].[Column].value('Name[1]','VARCHAR(50)') AS Name, --ATTRIBUTE
        [Table].[Column].value('Duration[1]','VARCHAR(50)') AS Duration, --TAG
        [Table].[Column].value('RowNo[1]','VARCHAR(50)') AS RowNo, --TAG
        [Table].[Column].value('Year1[1]','VARCHAR(50)') AS Year1, --ATTRIBUTE
        [Table].[Column].value('Year2[1]','VARCHAR(50)') AS Year2, --TAG
        [Table].[Column].value('Year3[1]','VARCHAR(50)') AS Year3, --TAG
        [Table].[Column].value('Year4[1]','VARCHAR(50)') AS Year4, --ATTRIBUTE
        [Table].[Column].value('Year5[1]','VARCHAR(50)') AS Year5, --TAG
        [Table].[Column].value('Avg3Year[1]','VARCHAR(50)') AS Avg3Year, --TAG
        [Table].[Column].value('Avg5Year[1]','VARCHAR(50)') AS Avg5Year, --ATTRIBUTE
        [Table].[Column].value('CurrentYear[1]','VARCHAR(50)') AS CurrentYear, --TAG
        [Table].[Column].value('CurrentYear1[1]','VARCHAR(50)') AS CurrentYear1, --TAG
        [Table].[Column].value('CurrentYear2[1]','VARCHAR(50)') AS CurrentYear2, --TAG
        [Table].[Column].value('CurrentYear3[1]','VARCHAR(50)') AS CurrentYear3, --TAG
        [Table].[Column].value('CurrentYear4[1]','VARCHAR(50)') AS CurrentYear4, --TAG
        [Table].[Column].value('CurrentYear5[1]','VARCHAR(50)') AS CurrentYear5, --ATTRIBUTE
        [Table].[Column].value('Current3YearAvg[1]','VARCHAR(50)') AS Current3YearAvg, --TAG
        [Table].[Column].value('Current5YearAvg[1]','VARCHAR(50)') AS Current5YearAvg --TAG
     FROM
        @xml.nodes('USCC/Row') as [Table]([Column])
END

My XML looks like :

<USCC>
   <Row Name = "BTA" Duration = "3" RowNo = "7" 
        Year1 = "0" Year2 = "0" Year3 = "0" Year4 = "0" Year5 = "0" 
        Avg3Year = "0" Avg5Year = "0" 
        CurrentYear ="2.36%" CurrentYear1 ="2.36%" 
        CurrentYear2 ="2.36%" CurrentYear3 ="2.36%" 
        CurrentYear4 ="2.36%" CurrentYear5 ="2.36%" 
        Current3YearAvg="2.36%" Current5YearAvg="2.36%"/>

   <Row Name = "Green" Duration = "16" RowNo = "7" 
        Year1 = "0" Year2 = "0" Year3 = "0" Year4 = "0" Year5 = "0" 
        Avg3Year = "0" Avg5Year = "0" 
        CurrentYear ="2.36%" CurrentYear1 ="2.36%" 
        CurrentYear2 ="2.36%" CurrentYear3 ="2.36%" 
        CurrentYear4 ="2.36%" CurrentYear5 ="2.36%" 
        Current3YearAvg="2.36%" Current5YearAvg="2.36%"/>
</USCC>

However it is inserting null values to table.

Any help. What am I doing wrong ?

Thanks, Swapnil

Upvotes: 1

Views: 43

Answers (1)

marc_s
marc_s

Reputation: 754268

You need to use XML attributes - identified by a leading @ - (and not XML elements) to get your individual data elements:

SELECT
   [Table].[Column].value('@Name', 'VARCHAR(50)') AS Name, --ATTRIBUTE
   [Table].[Column].value('@Duration', 'VARCHAR(50)') AS Duration, --TAG

Upvotes: 2

Related Questions