question

nvmb64 avatar image
nvmb64 asked

how to pass a xml data record dynamically in to a parameter

Hi all, I just want to know how to send a xml file into a parameter dynamically ! Is this possible? If yes please reply me --------------------------- i first set @parameter1 = 1 Orlando N. Gee A Bike Store adventure-works\pamela0 orlando0@adventure-works.com 245-555-0173 now i need to put this @parameter1 into a row in a table . Now again i need to retrieve it from from the table into a parameter again . like @parameter2= @parameter1
sql-server-2008sqlxml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
I doubt you've explained your use case very well. Seeing as the question you have asked is 'can I put the value of a parameter into a table and get it back out again?', then I'll answer that question. To put the value of the parameter into a table INSERT INTO myTable (myColumn) VALUES (@parameter) To get it back out SELECT @parameter = myColumn FROM myTable WHERE some_stuff
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
to combine your question and Matt's answer into an example: DECLARE @parameter1 XML set @parameter1 = ' 1 Orlando N. Gee A Bike Store adventure-works\pamela0 orlando0@adventure-works.com 245-555-0173 ' DECLARE @Table TABLE (MyCol xml) INSERT INTO @Table ( [MyCol] ) VALUES ( @parameter1 ) DECLARE @parameter2 XML SELECT @parameter2 = mycol FROM @Table AS t select @parameter2
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nvmb64 avatar image nvmb64 commented ·
Hi all, I think i have misguided you people. my problem is i need to set a parameter with the xml data coming from a table, like set @parameter1 = select * from dbo.DB_CUSTOMERS for XML PATH , ROOT ('Customers') and then i need to assign the data in the @parameter1 to another parameter named @parameter2 like set @parameter1 = @parameter2 but this is throwing a error ! Guess i made you people clear this time. Thank you, Mahendra
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
Then this works when added to the existing code in my answer DECLARE @paramter3 XML SELECT @paramter3 = @parameter2 SELECT @paramter3 or you could use DECLARE @paramter3 XML SET @paramter3 = (SELECT @parameter2) SELECT @paramter3
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
Just in case you were after a way of taking the XML and pushing it into a table structure, and then exporting to XML at a later date, take a look at this: /* Test Data - extended to prove multiple entries can work too */ DECLARE @xml xml = ' 1 Orlando N. Gee A Bike Store adventure-works\pamela0 orlando0@adventure-works.com 245-555-0173 2 Someone Else Another Bike Store adventure-works\paula Someone0@adventure-works.com 245-555-0174 ' /* Test Import Table */ DECLARE @TestTable AS TABLE ([CustomerID] int, [FirstName] varchar(100), [MiddleName] varchar(100), [LastName] varchar(100), [CompanyName] varchar(100), [SalesPerson] varchar(100), [EmailAddress] varchar(100), [Phone] varchar(100)) INSERT INTO @TestTable SELECT items.item.value('CustomerID[1]', 'varchar(100)') CustomerID, items.item.value('FirstName[1]', 'varchar(100)') FirstName, items.item.value('MiddleName[1]', 'varchar(100)') MiddleName, items.item.value('LastName[1]', 'varchar(100)') LastName, items.item.value('CompanyName[1]', 'varchar(100)') CompanyName, items.item.value('SalesPerson[1]', 'varchar(100)') SalesPerson, items.item.value('EmailAddress[1]', 'varchar(100)') EmailAddress, items.item.value('Phone[1]', 'varchar(100)') Phone FROM @xml.nodes('//Customers/row') items(item); /* Export from the Table in the same shape as the import file */ SELECT CustomerID, FirstName, MiddleName, LastName, CompanyName, SalesPerson, EmailAddress, Phone FROM @TestTable FOR XML PATH, ROOT ('Customers')
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nvmb64 avatar image nvmb64 commented ·
Hello Wiliam , What you said was assigning the xml data directly into @xml parameter, but my requirement is to assign the xml data from a table in my database i think i made it clear for you, thanks, mahendra
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.