CODEWITHSUNDEEP
sql-serverxml
user5087404
user5087404

Reputation:

Getting null values into the table while importing XML Data into a table

I'm trying to import a XML file into a table in SQL Server.

Here is the code I wrote:

Declare @x xml

Select @x = p
            from OpenRowSet(BULK 'C:\Users\sivat\OneDrive\Projects\SQL Server     Projects\Books.xml',SINGLE_BLOB) as BOOKS(p)

Declare @hdoc int

exec sp_xml_preparedocument @hdoc output,@x

IF OBJECT_ID('BOOKS') IS NOT NULL 
     DROP Table BOOKS

select *
into BOOKS
from openxml(@hdoc,'/catalog/book',2)
with
(   Author nvarchar(150),
    Title nvarchar(150),
    Genre nvarchar(150),
    Price float,
    Publish_Date Date,
    Description nvarchar(200))
exec sp_xml_removedocument @hdoc
select * from BOOKS

I ended up getting null values into the table ..

What modifications do I need to do?

Here is the original XML file:

<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies, 
      an evil sorceress, and her own childhood to become queen 
      of the world.</description>
   </book>
   <book id="bk103">
      <author>Corets, Eva</author>
      <title>Maeve Ascendant</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-11-17</publish_date>
      <description>After the collapse of a nanotechnology 
      society in England, the young survivors lay the 
      foundation for a new society.</description>
   </book>
   <book id="bk104">
      <author>Corets, Eva</author>
      <title>Oberon's Legacy</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2001-03-10</publish_date>
      <description>In post-apocalypse England, the mysterious 
      agent known only as Oberon helps to create a new life 
      for the inhabitants of London. Sequel to Maeve 
      Ascendant.</description>
   </book>
   <book id="bk105">
      <author>Corets, Eva</author>
      <title>The Sundered Grail</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2001-09-10</publish_date>
      <description>The two daughters of Maeve, half-sisters, 
      battle one another for control of England. Sequel to 
      Oberon's Legacy.</description>
   </book>
   <book id="bk106">
      <author>Randall, Cynthia</author>
      <title>Lover Birds</title>
      <genre>Romance</genre>
      <price>4.95</price>
      <publish_date>2000-09-02</publish_date>
      <description>When Carla meets Paul at an ornithology 
      conference, tempers fly as feathers get ruffled.</description>
   </book>
   <book id="bk107">
      <author>Thurman, Paula</author>
      <title>Splish Splash</title>
      <genre>Romance</genre>
      <price>4.95</price>
      <publish_date>2000-11-02</publish_date>
      <description>A deep sea diver finds true love twenty 
      thousand leagues beneath the sea.</description>
   </book>
   <book id="bk108">
      <author>Knorr, Stefan</author>
      <title>Creepy Crawlies</title>
      <genre>Horror</genre>
      <price>4.95</price>
      <publish_date>2000-12-06</publish_date>
      <description>An anthology of horror stories about roaches,
      centipedes, scorpions  and other insects.</description>
   </book>
   <book id="bk109">
      <author>Kress, Peter</author>
      <title>Paradox Lost</title>
      <genre>Science Fiction</genre>
      <price>6.95</price>
      <publish_date>2000-11-02</publish_date>
      <description>After an inadvertant trip through a Heisenberg
      Uncertainty Device, James Salway discovers the problems 
      of being quantum.</description>
   </book>
   <book id="bk110">
      <author>O'Brien, Tim</author>
      <title>Microsoft .NET: The Programming Bible</title>
      <genre>Computer</genre>
      <price>36.95</price>
      <publish_date>2000-12-09</publish_date>
      <description>Microsoft's .NET initiative is explored in 
      detail in this deep programmer's reference.</description>
   </book>
   <book id="bk111">
      <author>O'Brien, Tim</author>
      <title>MSXML3: A Comprehensive Guide</title>
      <genre>Computer</genre>
      <price>36.95</price>
      <publish_date>2000-12-01</publish_date>
      <description>The Microsoft MSXML3 parser is covered in 
      detail, with attention to XML DOM interfaces, XSLT processing, 
      SAX and more.</description>
   </book>
   <book id="bk112">
      <author>Galos, Mike</author>
      <title>Visual Studio 7: A Comprehensive Guide</title>
      <genre>Computer</genre>
      <price>49.95</price>
      <publish_date>2001-04-16</publish_date>
      <description>Microsoft Visual Studio 7 is explored in depth,
      looking at how Visual Basic, Visual C++, C#, and ASP+ are 
      integrated into a comprehensive development 
      environment.</description>
   </book>
</catalog>

Upvotes: 1

Views: 174

Answers (1)

marc_s
marc_s

Reputation: 754258

I would prefer to use the native, built-in XQuery methods rather than the clunky, memory-leaking OPENXML stuff.....

Try this code:

DECLARE @x XML

SELECT @x = p FROM OpenRowSet(BULK 'C:\Users\sivat\OneDrive\Projects\SQL Server Projects\Books.xml',SINGLE_BLOB) as BOOKS(p)

SELECT
    Author = xc.value('(author)[1]', 'nvarchar(150)'),
    Title = xc.value('(title)[1]', 'nvarchar(150)'),
    Genre = xc.value('(genre)[1]', 'nvarchar(150)'),
    Price = xc.value('(price)[1]', 'decimal(15,2)'),
    Publish_Date = xc.value('(publish_date)[1]', 'date'),
    Description = xc.value('(genre)[1]', 'nvarchar(200)')
FROM
    @x.nodes('/catalog/book') AS XT(XC)

Of course, this only displays the values - but you can easily replace the final SELECT with this: 

INSERT INTO dbo.Books(Author, Title, Genre, Price, Publish_Date, Description)
    SELECT
        Author = xc.value('(author)[1]', 'nvarchar(150)'),
        Title = xc.value('(title)[1]', 'nvarchar(150)'),
        Genre = xc.value('(genre)[1]', 'nvarchar(150)'),
        Price = xc.value('(price)[1]', 'decimal(15,2)'),
        Publish_Date = xc.value('(publish_date)[1]', 'date'),
        Description = xc.value('(genre)[1]', 'nvarchar(200)')
    FROM
        @x.nodes('/catalog/book') AS XT(XC)

and insert your data into the table dbo.Books (which has to exist before running this statement).

Also note: I changed the datatype for Price from float (which is notoriously susceptible to rounding errors) and picked decimal(15,2) instead (which has none of those rounding errors)

Upvotes: 1

Related Questions