开发者

Querying a xml in sql server 2008

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('.','tex开发者_StackOverflow中文版t')
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</Image_URL> 
  <title>QUICK AND FLUPKE</title> 
  <Description>QUICK AND FLUPKE </Description> 
  </booksdetail>
  <booksdetail>...</booksdetail>

Problem: It is not doing anything on the table.


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜