Querying a xml in sql server 2008

Question

Hi all,

I wan t to upload the xml data to the sql table. I am passing the xml data from .net(c#)(Working fine)

code:

 ALTER PROCEDURE [dbo].[BookDetails_Insert] (@xml xml)

 AS
 insert into BookDetails (Pages,Descriptions) 
  SELECT 
    ParamValues.PLName.value('.','int'),
    ParamValues.PLDEscr.value('.','text')
FROM 
    @xml.nodes('/booksdetail/Pages') as ParamValues(PLName)
            @xml.nodes('/booksdetail/Description') as ParamValues(PLName, PLDEscr)

xml Code:

  <booksdetail>
  <isbn_13>70100048</isbn_13> 
  <isbn_10>00048B</isbn_10> 
  <Image_URL>http://www.thenet.com/Book/large/00/701001048.jpg&lt;/Image_URL&gt; 
  <title>QUICK AND FLUPKE</title> 
  <Description>QUICK AND FLUPKE </Description> 
  </booksdetail>
  <booksdetail>...</booksdetail>

Problem: It is not doing anything on the table.

Answer 1

Your sample xml does not have a Pages node. This means that when the FROM clause is computed, a cross join is formed between a list of zero rows and a list of one row. The resulting product has no rows, so there is nothing SELECTed, so nothing is INSERTed.

If you actually want to insert one row into BookDetails for each booksdetail node in the incoming xml, you should do something like

SELECT 
    ParamValues.PL.value('Pages[1]','int'),
    CAST(ParamValues.PLr.value('Description[1]','varchar(max)') AS text)
FROM 
    @xml.nodes('/booksdetail') as ParamValues(PL)

That is, shred the incoming xml into booksdetail nodes, and pull out each of the .values you want from these rows.