none
how to pass a xml data record dynamically into a parameter

    Question

  • Hi all,

    I just want to know how to send a xml record in a table into a parameter dynamically ! Is this possible? If yes please reply me

    ______________________________________________________________________________________________________

     i need to set @parameter1 = select * from dbo.DB_CUSTOMERS for XML PATH , ROOT ('Customers')

    Thursday, April 14, 2011 11:50 AM

Answers

  • You can do it the following way:

    DECLARE @XML xml =
     (SELECT    Resume
       FROM [AdventureWorks2008].[HumanResources].[JobCandidate]
       WHERE BusinessEntityID=274 )
    SELECT @XML
    GO
    
    /*
    <ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">
     <ns:Name>
      <ns:Name.Prefix>Mr.</ns:Name.Prefix>
      <ns:Name.First>Stephen</ns:Name.First>
      <ns:Name.Middle>Y </ns:Name.Middle>
      <ns:Name.Last>Jiang</ns:Name.Last>
      <ns:Name.Suffix />
     </ns:Name>
     <ns:Skills> Considerable expertise in all areas of the sales cycle. 13 years of achievement in increasing organization revenue. Experience in sales planning and forecasting, customer development, and multiple selling techniques. 5 years of experience in sales management, including lead generation, sales force leadership, and territory management. Leverage management style and technical ability to effectively manage and communicate with a distributed sales force composed of field representatives, product demonstrators, and vendors.
    Excellent communication and presentation skills.
    .... */
    
    

    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    • Marked as answer by Ai-hua Qiu Monday, April 25, 2011 7:16 AM
    Thursday, April 14, 2011 1:09 PM
    Moderator

All replies

  • Can you please elaborate more on your requirement as your select query might return more than 1 column or row and the resultant record-set can not be set into a scalar variable @parameter.

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    • Edited by vinaypugalia Thursday, April 14, 2011 12:22 PM typo
    Thursday, April 14, 2011 12:21 PM
  • You can do it the following way:

    DECLARE @XML xml =
     (SELECT    Resume
       FROM [AdventureWorks2008].[HumanResources].[JobCandidate]
       WHERE BusinessEntityID=274 )
    SELECT @XML
    GO
    
    /*
    <ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">
     <ns:Name>
      <ns:Name.Prefix>Mr.</ns:Name.Prefix>
      <ns:Name.First>Stephen</ns:Name.First>
      <ns:Name.Middle>Y </ns:Name.Middle>
      <ns:Name.Last>Jiang</ns:Name.Last>
      <ns:Name.Suffix />
     </ns:Name>
     <ns:Skills> Considerable expertise in all areas of the sales cycle. 13 years of achievement in increasing organization revenue. Experience in sales planning and forecasting, customer development, and multiple selling techniques. 5 years of experience in sales management, including lead generation, sales force leadership, and territory management. Leverage management style and technical ability to effectively manage and communicate with a distributed sales force composed of field representatives, product demonstrators, and vendors.
    Excellent communication and presentation skills.
    .... */
    
    

    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    • Marked as answer by Ai-hua Qiu Monday, April 25, 2011 7:16 AM
    Thursday, April 14, 2011 1:09 PM
    Moderator