Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 30-Jul-18 20:48pm
Comments
CHill60 30-Jul-18 18:50pm    
And what is that error ?

1 solution

SQL
<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--;
 
Share this answer
 
Comments
Member 12640353 1-Aug-18 5:49am    
XML parsing: line 2, character 49, A string literal was expected
Santosh kumar Pithani 1-Aug-18 6:26am    
Have your executed solution query?
Member 12640353 1-Aug-18 9:27am    
yes i have executed the query.
DECLARE @sqxml XML, @xid INT;
SET @sqxml= N'<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 student.Typeid =CTE.scienceid

I am getting error XML parsing: line 2, character 49, A string literal was expected. Actually i want join Typeid with scienceid
Santosh kumar Pithani 2-Aug-18 0:33am    
Hello, your done small mistake i.e..given "<\education>"
instead of "</education>" .Please check once solution query.
Member 12640353 3-Aug-18 1:24am    
Thanks.. It is working

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900