Post reply

Setting the value of an XML attribute using a relational variable

  • November 15, 2012 at 9:40 am

    #284954

    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

  • November 15, 2012 at 2:20 pm

    #1559534

    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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply