Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Setting the value of an XML attribute using a relational variable Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 9:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:18 AM
Points: 2,048, Visits: 1,403
Is it possible to set the value of an XML attribute in an XML fragment using a relational variable that is declared and initialised elsewhere in the code block or stored procedure, e.g.

DECLARE @FullPathAndName varchar(100)  
SET @FullPathAndName = 'C:\DBA_Share\SSRS\Output\MyReport.rpt'

DECLARE @xVar XML 
SET @xVar =
'<package name="\File System\ReportToExcel" owner="sa" runAs="">
<variable name="Cnfg_ReportPath" value="C:\DBA_Share\SSRS\"/>
<variable name="vSourceQuery" value="select * from dbo.sometable"/>
<variable name="vFileName" value="@FullFileName"/>
</package>'

I know it is possible to use {sql:variable} when building an xml fragment from a query, e.g.

DECLARE @price money=2500.00
SELECT ProductID, Production.ProductModel.ProductModelID,CatalogDescription.query('
declare namespace pd="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

<Product
ProductID="{ sql:column("Production.Product.ProductID") }"
ListPrice="{ sql:column("Production.Product.ListPrice") }"
DiscountPrice="{ sql:variable("@price") }"
/>')
FROM Production.Product

..but that's not what I want to do here.

Thanks
Lempster
Post #1385250
Posted Thursday, November 15, 2012 2:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:10 AM
Points: 2,587, Visits: 1,626
If i understand you correctly, you have an xml instance as a string which you want to assign directly to an xml variable whilst at the same time, populating some nodes in the xml string with other variables from the transaction scope? I'm probably not entirely understanding everything, but I have come up with this:

DECLARE @FullPathAndName varchar(100)  
SET @FullPathAndName = 'C:\DBA_Share\SSRS\Output\MyReport.rpt'

DECLARE @xVar XML = ''

SELECT @xVar = @xVar.query('for $x in ("") return
<package name="\File System\ReportToExcel" owner="sa" runAs="">
<variable name="Cnfg_ReportPath" value="C:\DBA_Share\SSRS\"/>
<variable name="vSourceQuery" value="select * from dbo.sometable"/>
<variable name="vFileName" value="{sql:variable("@FullPathAndName")}"/>
</package>')

SELECT @xVar

Does this help? if not, then if you can go into a bit more of the scenario that you have and what is your end goal.
Post #1385367
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse