如何使用表加入XML属性 [英] How do I join XML attributes with table

问题描述

我需要使用基础表格加入XML



声明sqxml xml ='< education>

< Type scienceid =' 16'onscienceid =51class ='A'/>



< type scienceid ='18'noncienceid =50class ='B'/ >



< Type scienceid ='16'onscienceid =50class ='C'/>

'





SELECT tuscienceid，来自学生的tunonscienceid加入student.Type @ sqlxml.node上的Sqlxml [/ Education / Type / @ scienceid =' 16']



我尝试过：



声明sqxml xml ='< education>

< Type scienceid ='16'onscienceid =51class ='A'/>



<类型scienceid ='18'noncienceid =50class ='B'/>



< Type scienceid ='16'onscienceid =50 class ='C'/>

'





SELECT tuscienceid，来自st的tunonscienceid在student.Type @ sqlxml.node上加入Sqlxml [/ Education / Type / @ scienceid = '16']



i AM获取XQUERY ERROR

i NEED TO JOIN XML WITH BASE TABLE

Declare sqxml xml = '<education>
<Type scienceid ='16' nonscienceid="51" class='A'/>

<Type scienceid ='18' nonscienceid="50" class='B'/>

<Type scienceid ='16' nonscienceid="50" class='C'/>
'


SELECT t.u.scienceid,t.u.nonscienceid from student join Sqlxml on student.Type @sqlxml.node[/Education/Type/@scienceid ='16']

What I have tried:

Declare sqxml xml = '<education>
<Type scienceid ='16' nonscienceid="51" class='A'/>

<Type scienceid ='18' nonscienceid="50" class='B'/>

<Type scienceid ='16' nonscienceid="50" class='C'/>
'


SELECT t.u.scienceid,t.u.nonscienceid from student join Sqlxml on student.Type @sqlxml.node[/Education/Type/@scienceid ='16']

i AM GETTING XQUERY ERROR

推荐答案

<pre>
DECLARE @sqxml XML, @xid INT;
SET  @sqxml= '<education>
                <Type scienceid =''16'' nonscienceid=''51'' class=''A''/>
                
                <Type scienceid =''18'' nonscienceid=''50'' class=''B''/>
                
                <Type scienceid =''16'' nonscienceid=''50'' class=''C''/>
              </education>';

exec sp_xml_prepareDocument @xid OUTPUT,@sqxml

;WITH CTE AS(SELECT * FROM OPENXML(@xid,'education/*') 
                                WITH (scienceid VARCHAR(10) '@scienceid',
                                      nonscienceid VARCHAR(10) '@nonscienceid',
						              class VARCHAR(10) '@class'
									  )
									  WHERE scienceid=16
			)

 SELECT * FROM CTE --INNER JOIN Student on(condtions)
--Thank you--;

这篇关于如何使用表加入XML属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！