在sql server 2008中查询xml
我想将xml数据上传到sql表。我从 .net(c#)(工作正常)
代码传递 xml 数据:
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>
问题:它没有在表上执行任何操作。
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</Image_URL>
<title>QUICK AND FLUPKE</title>
<Description>QUICK AND FLUPKE </Description>
</booksdetail>
<booksdetail>...</booksdetail>
Problem: It is not doing anything on the table.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您的示例 xml 没有
Pages
节点。这意味着当计算FROM
子句时,会在零行列表和一行列表之间形成交叉联接。生成的结果没有行,因此没有任何SELECT
编辑,因此没有任何INSERT
编辑。如果您确实想在传入 xml 中的每个
booksdetail
节点的BookDetails
中插入一行,您应该执行类似的操作,即,粉碎将传入的 xml 放入
booksdetail
节点,并从这些行中提取所需的每个.value
。Your sample xml does not have a
Pages
node. This means that when theFROM
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 nothingSELECT
ed, so nothing isINSERT
ed.If you actually want to insert one row into
BookDetails
for eachbooksdetail
node in the incoming xml, you should do something likeThat is, shred the incoming xml into
booksdetail
nodes, and pull out each of the.value
s you want from these rows.