SQL Server variable in XML

I have code like this:

declare @Temp TABLE (text varchar(max));

DECLARE @xml XML = N'<tt>
   <cpost s="a" cena="0.0000" cpost_id="16385" flprt="1" moq="0"
valuta_id="2" nmatr_id="14117" norg_id="1791" />
   <cpost s="a" norg_id="3883" nmatr_id="14117" valuta_id="2" moq="0"
flprt="0" cpost_id="9269" cena="0.0000" />
   <gx2tov s="a" gx_id="1" gx2tov_id="12435" nmatr_id="14117" />
   <kodp s="a" kodp_id="16496" nmatr_id="14117" norg_id="1847"
primtxt="дали на ДСК как 607194 (7863)" />
   <Sertif1 s="a" nmatr_id="14117" sertif_id="154" sertif1_id="13247" />
   <Sertif1 s="a" sertif1_id="13495" sertif_id="154" nmatr_id="14117" />
   <slmat s="a" akciz="0.00" artic="PP-46100" barcod3="4690401014360"
brand_id="7" c_prod="1.9200" c_zak="0.6290" dlina1="40.00"
dlina2="13.00" dlina21="0.00" dlina3="0.000" ed1_id="5" ed2_id="3"
ed3_id="12" Flbcdublno="0" fllock="0" flview="0" flweb="1"
fotost_id="46" karlst_id="9" kfp112="24" kfp12="24.00" kfp23="48.00"
kfs_id="1" kubfut="0.9041" kubfut2="0.0000" kubfut3="0.0000"
kubm="0.0256" kubm2="0.0000" kubm3="0.0000" man_id="234" maxkor="0"
minkor="0" nazv_id="8764" ngrup_id="16" nmatr_id="14117" osob_id="3"
pr_id="9" prcprib="0.0" razmer_id="23" shirina1="32.00" shirina2="5.00"
shirina21="0.00" shirina3="0.00" sitelink="/get-image/?xml_id=14117"
sortt_id="7402" tema_id="26" typecen_id="1" typeob_id="16"
typeper_id="3" val_id_pr="2" val_id_zak="2" vesupkg="4.70"
vesupkg2="0.18" vesupkg3="0.00" vesupkgn="4.22" vesupkgn2="0.00"
vesupkgn3="0.00" vidt_id="589" vysota1="20.00" vysota2="16.50"
vysota21="0.00" vysota3="0.00" />
   </tt>';

INSERT INTO @Temp
    SELECT '{$t/'+ attribute + '}' 
    FROM third
    ORDER BY number;

--select * from @Temp
SELECT @xml.query
('
    let $t:=/tt
    return
    <tt>
    +sql:variable(@Temp)    
    </tt>
');

--[sql:variable("@Count")]

It should be returning:

  • User defined function performance disadvantages
  • Rollback for bulk copy
  • Incorrect syntax near 'THROW'
  • How do I convert an Oracle TIMESTAMP data type to SQL Server DATETIME2 data type while connected via Link Server.?
  • Create users dynamic names and assgin roles
  • Reporting Services Chart - Custom Axis Label
  • SELECT @xml.query
    ('
        let $t:=/tt
        return
        <tt>
        {$t/slmat}
        {$t/cpost}
        {$t/kodp}
        {$t/gx2tov}
        {$t/Sertif1}
        </tt>
    ');
    

    but it’s returning:

    <tt>
        +sql:variable(@Temp)    
    </tt>
    

  • SQL Server login failure in middle of application run
  • Do Inserted Records Always Receive Contiguous Identity Values
  • Enable/Disable Sql Server Agent using a t-sql script
  • Can I force a Chart at the top of a report to always be visible
  • Creating a recursive CTE with no rootrecord
  • Date Format in SQL dd/mm/yyyy hr:mm:ss AM/PM
  • 2 Solutions collect form web for “SQL Server variable in XML”

    My magic crystall ball tells me, that you want to sort your elements according to an external list (a table). You might try it like this:

    DECLARE @xml XML = 
    N'<tt>
      <cpost s="a" cena="0.0000" cpost_id="16385" flprt="1" moq="0" valuta_id="2" nmatr_id="14117" norg_id="1791" />
      <cpost s="a" norg_id="3883" nmatr_id="14117" valuta_id="2" moq="0" flprt="0" cpost_id="9269" cena="0.0000" />
      <gx2tov s="a" gx_id="1" gx2tov_id="12435" nmatr_id="14117" />
      <kodp s="a" kodp_id="16496" nmatr_id="14117" norg_id="1847" primtxt="дали на ДСК как 607194 (7863)" />
      <Sertif1 s="a" nmatr_id="14117" sertif_id="154" sertif1_id="13247" />
      <Sertif1 s="a" sertif1_id="13495" sertif_id="154" nmatr_id="14117" />
      <slmat s="a" akciz="0.00" artic="PP-46100" barcod3="4690401014360" brand_id="7" c_prod="1.9200" c_zak="0.6290" dlina1="40.00" dlina2="13.00" dlina21="0.00" dlina3="0.000" ed1_id="5" ed2_id="3" ed3_id="12" Flbcdublno="0" fllock="0" flview="0" flweb="1" fotost_id="46" karlst_id="9" kfp112="24" kfp12="24.00" kfp23="48.00" kfs_id="1" kubfut="0.9041" kubfut2="0.0000" kubfut3="0.0000" kubm="0.0256" kubm2="0.0000" kubm3="0.0000" man_id="234" maxkor="0" minkor="0" nazv_id="8764" ngrup_id="16" nmatr_id="14117" osob_id="3" pr_id="9" prcprib="0.0" razmer_id="23" shirina1="32.00" shirina2="5.00" shirina21="0.00" shirina3="0.00" sitelink="/get-image/?xml_id=14117" sortt_id="7402" tema_id="26" typecen_id="1" typeob_id="16" typeper_id="3" val_id_pr="2" val_id_zak="2" vesupkg="4.70" vesupkg2="0.18" vesupkg3="0.00" vesupkgn="4.22" vesupkgn2="0.00" vesupkgn3="0.00" vidt_id="589" vysota1="20.00" vysota2="16.50" vysota21="0.00" vysota3="0.00" />
    </tt>';
    
    DECLARE @SortOrder TABLE(element VARCHAR(100),inx INT);
    INSERT INTO @SortOrder VALUES
     ('slmat',1)
    ,('cpost',2)
    ,('kodp',3)
    ,('gx2tov',4)
    ,('Sertif1',5);
    
    SELECT --a.value(N'local-name(.)',N'nvarchar(max)') AS ElementName
            a.query(N'.') AS [node()]
            --so.inx
    FROM @xml.nodes(N'/tt/*') A(a)
    INNER JOIN @SortOrder AS so ON a.value(N'local-name(.)',N'nvarchar(max)')=so.element
    ORDER BY so.inx
    FOR XML PATH(N''),ROOT(N'tt')
    

    The sql:variable function will only expose a value, not a table (a table is not a value).

    Exposes a variable that contains a SQL relational value inside an XQuery expression.

    I think you’ll be needing the sql:column function.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.