在 sql server 2008 中查询 xml [英] Querying a xml in sql server 2008

问题描述

我不想将xml数据上传到sql表.我正在从 .net(c#) 传递 xml 数据(工作正常)

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

代码:

 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 代码:

  <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.

推荐答案

您的示例 xml 没有 Pages 节点.这意味着当计算 FROM 子句时，在零行列表和一行列表之间形成交叉连接.生成的产品没有行，所以没有任何 SELECTed，所以没有任何 INSERTed.

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.

如果你真的想为传入的 xml 中的每个 booksdetail 节点在 BookDetails 中插入一行，你应该做类似的事情

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)

也就是说，将传入的 xml 分解为 booksdetail 节点，并从这些行中提取您想要的每个 .value.

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

这篇关于在 sql server 2008 中查询 xml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！