Solved

how to pass dynamic value to root node attribute of XML using Xquery?

Posted on 2010-08-18
4
892 Views
Last Modified: 2012-05-10
Code Snippet:


DECLARE @TempTable TABLE  
  (  
      _tagName VARCHAR(25),
    _value1 VARCHAR(50),  
    _value2 VARCHAR(50)  
  )  

Declare @valuation varchar(25)

SET @valuation = '123'

INSERT @TempTable SELECT 'TEST', 'FScore', '123456789'  
INSERT @TempTable SELECT 'TEST', 'EstimatedMarketingTime', '123512'

SELECT (SELECT
      _value1 AS [_Name],  
    _value2 AS [_Value],
      @valuation
FROM @TempTable AS A WHERE _TagName = 'TEST'                  
FOR XML PATH('TEST'),      TYPE).query('  
      <TEST PropertyAppraisedValueAmount="Need to pass Variable Value">  
      {  
            for $TEST in /TEST  
            return
               <Key _Name="{data($TEST/_Name)}" _Value="{data($TEST/_Value)}"></Key>
      }
      </TEST>')
GO

Expected:
In the tag <TEST PropertyAppraisedValueAmount="Need to pass Variable Value">, "Need to pass Variable Value" has to be replaced with the variable @valuation.
Note:
The @valuation will be dynamic in the real scenario but for sample I have hard coded the value to'123'.

0
Comment
Question by:sureshbabukrish
  • 2
4 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
Comment Utility

DECLARE @TempTable TABLE  

  (  

      _tagName VARCHAR(25),

    _value1 VARCHAR(50),  

    _value2 VARCHAR(50)  

  )  



Declare @valuation varchar(25)



SET @valuation = '123'



INSERT @TempTable SELECT 'TEST', 'FScore', '123456789'  

INSERT @TempTable SELECT 'TEST', 'EstimatedMarketingTime', '123512'



SELECT (SELECT

      _value1 AS [_Name],  

    _value2 AS [_Value],

      @valuation

FROM @TempTable AS A WHERE _TagName = 'TEST'                  

FOR XML PATH('TEST'), TYPE).query('

      <TEST PropertyAppraisedValueAmount="{ sql:variable("@valuation") }">

      {  

            for $TEST in /TEST  

            return

               <Key _Name="{data($TEST/_Name)}" _Value="{data($TEST/_Value)}"></Key>

      }

      </TEST>')

GO

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Nice one!  A good example of creating dynamic attributes without using Dynamic SQL, if ever I saw one.  :)
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
And it was only weeks ago that I was schooled on how to take sql:variable into xquery .. queries.
0
 
LVL 9

Author Closing Comment

by:sureshbabukrish
Comment Utility
it answer for the question i asked.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now