SP with xml data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SP with xml data

Author

Topic

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2011-04-11 : 06:00:10

Hi
I have a storedprocedure to insert data into a table and the data to insert is passes as xml. How can I make one more selection while inserting from the xml data ie

My sp is
CREATE PROCEDURE sp_update
(
@TCdetailsXML XML
)
AS
BEGIN
insert into SalesQuotation(quotationId,TCid,TC)
select
TCdetails.Details.value('quotationId[1]','numeric(9)') as quotationId,
TCdetails.Details.value('TCId[1]','numeric(9)') as TCid,
TCdetails.Details.value('TC[1]','varchar(500)')as TC
from @TCdetailsXML.nodes('//TCDetailsList/TCDetails') as TCdetails(Details)
END

sample xml input
'<TCDetailsList>
<TCDetails><quotationId>164</quotationId><TCId>Term1</TCId><TC>g1</TC></TCDetails>
<TCDetails><quotationId>164</quotationId><TCId>Term2</TCId><TC>g2</TC></TCDetails>
</TCDetailsList>'

in the above sp I want to insert the TCId but not Term1 or Term2 for that I want to make a select query to get the TCId like
select TCId from TCtable where TCName ='Term1'
How can I incorporate this selection along in the above SP

Thanks and Regards
Anu Palavila

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-11 : 07:31:32

It is not completely clear to me what the logic is. So copy this code, and run it - it won't insert anything into the table - it will just show you what it would insert. If that looks right, then you can uncomment the line that starts with --insert into.

Also, if there is no corresponding row in TCTable, this will not insert a row into SalesQuotation. Similarly, if there are multiple rows in the TCTable that matches the TCId, then it will insert as many rows into the SalesQuotation table.

declare @TCdetailsXML  xml = 
'<TCDetailsList>
  <TCDetails>
    <quotationId>164</quotationId>
    <TCId>Term1</TCId>
    <TC>g1</TC>
  </TCDetails>
  <TCDetails>
    <quotationId>164</quotationId>
    <TCId>Term2</TCId>
    <TC>g2</TC>
  </TCDetails>
</TCDetailsList>';

with cte as
(
	select 
		TCdetails.Details.value('quotationId[1]','numeric(9)') as quotationId,
		TCdetails.Details.value('TCId[1]','varchar(255)') as TCid,
		TCdetails.Details.value('TC[1]','varchar(500)')as TC
	from 
		@TCdetailsXML.nodes('//TCDetailsList/TCDetails') as TCdetails(Details)
)
--insert into SalesQuotation(quotationId,TCid,TC)
select
	c.quotationId,
	a.TCId,
	c.TC
from
	cte c
	inner join TCTable t
	   on c.TCId = a.TCName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources