加载xml sql server ... [英] load xml sql server...

问题描述

大家好

 <   AllowanceDeduction  >  
 <  扣除   名称  =  ROW 1   金额  =   -  99    /  >  
 <   allowance    名称  =  ROW 2   金额  =  88 < span class =code-attribute>   /  >  
 <   / AllowanceDeduction  >  

我上面有一个xml架构







我想要的内容如下

 allowanceName allowanceamount deductionName dectionAmount 
 ROW-2 88 ROW 1 -99 

请帮帮我们..非常urgnet ...

谢谢

提前





邮件给我[电子邮件已删除 - SA ]

[重新发布检索va lml来自xml [ ^ ] - SA]

解决方案

像这样：

  DECLARE   @ myDoc  xml 
  SET   @ myDoc  = ' < AllowanceDeduction> 
<扣除名称=行1金额= -  99/> 
< allowance Name =ROW 2Amount =88/> 
< / AllowanceDeduction>' 
 
  select  
  @myDoc  .value（' （/ AllowanceDeduction / allowance / @ Name）[1]'，'  varchar（10）'） as  allowanceName，
  @ myDoc  .value（' （/ AllowanceDeduction / allowance / @ Amount）[1]'，'  int'） as  allowanceAmount，
  @ myDoc  .value（' （/ AllowanceDeduction / Deduction / @ Name）[1]'，'  varchar（10）'） as  deductionName，
  @ myDoc  .value（' （/ AllowanceDeduction / Deduction / @金额）[1]'，'  int'） as  deductionAmount 

不过，请记住：

1）我可能是对你很紧急，但我们都在这里帮助，我们在空闲时间这样做

2）如果你不喜欢垃圾邮件，永远不要发布这样的电子邮件地址...

hi all

<AllowanceDeduction>
  <Deduction Name=" ROW 1" Amount="-99" />
  <allowance Name="ROW 2" Amount="88" />
</AllowanceDeduction>

I have a xml schema above like that



I want out put as follows

allowanceName allowanceamount  deductionName dectionAmount
ROW-2                 88                             ROW 1               -99

Please help me guys ..its very urgnet...
thanks
in advance


mail me [e-mail removed — SA]
[Re-posted Retrieve values from xml[^] — SA]

解决方案

Like this:

DECLARE @myDoc xml
SET @myDoc = '<AllowanceDeduction>
  <Deduction Name=" ROW 1" Amount="-99" />
  <allowance Name="ROW 2" Amount="88" />
</AllowanceDeduction>'

select 
	@myDoc.value('(/AllowanceDeduction/allowance/@Name)[1]', 'varchar(10)' ) as allowanceName,
	@myDoc.value('(/AllowanceDeduction/allowance/@Amount)[1]', 'int' ) as allowanceAmount,
	@myDoc.value('(/AllowanceDeduction/Deduction/@Name)[1]', 'varchar(10)' ) as deductionName,
	@myDoc.value('(/AllowanceDeduction/Deduction/@Amount)[1]', 'int' ) as deductionAmount

Still, please remember:
1) I tmight be urgent for you, but we all are here to help, and we do this in out free time
2) Never ever publish yout email address like this, if you don't like spams...

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