Issue with XML Modify

Question

I am trying to generate an attribute using XML Modify of SQL server. The Attribute i am trying to generate is

HRef="#'2'!A1"

The Query that I am using is

set @xml.modify('declare default element namespace  "urn:schemas-microsoft-com:office:spreadsheet";
                declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
                declare namespace x="urn:schemas-microsoft-com:office:excel";
                insert attribute  ss:HRef {"#''sql:variable(""@worksheets"")''!A1"}      
                into (/Workbook/Worksheet[1]/Table/Row[sql:variable("@worksheets")]/Cell)[1]');

The sql:variable("@worksheets") is coming from a loop variable. and its value are 1, 2 , 3 for each loop. So the values that want in that attribute should be

HRef="#'1'!A1"
HRef="#'2'!A1"
HRef="#'3'!A1"

Now the issue is, the value is not changing in XML. If i am using following query

insert attribute  ss:HRef {"#''sql:variable(""@worksheets"")''!A1"}      
                into (/Workbook/Worksheet[1]/Table/Row[sql:variable("@worksheets")]/Cell)[1]');

The Attribute that is generated in XML is

ss:HRef="#'sql:variable("@worksheets")'!A1"

which is worng, it should be generated as

HRef="#'2'!A1"

As per my understanding the value of sql:variable("@worksheets") is not getting replaced. If i put some hard coded value instead of sql:variable("@worksheets") the values are visible correctly in xml. Can any one please suggest what wrong i am doing.

Sample XML that i am using is

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    <Styles xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
        <Style ss:ID="Default" ss:Name="Normal">
            <Alignment ss:Vertical="Bottom"/>
            <Borders/>
            <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
            <Interior/>
            <NumberFormat/>
            <Protection/>
        </Style>
        <Style ss:ID="s62">
            <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000" ss:Bold="1"/>
        </Style>
        <Style ss:ID="s63" ss:Name="Hyperlink">
            <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#0000FF" ss:Underline="Single"/>
        </Style>
    </Styles>
    <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="1">
        <Table xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:DefaultColumnWidth="125">
            <Column ss:AutoFitWidth="1"/>
            <Row xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">Worksheet #</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String"/>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">1</Data>
                </Cell>
            </Row>
            <Row xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:HRef="#'sql:variable(&quot;@worksheets&quot;)'!A1">
                    <Data ss:Type="String">1</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String"/>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">1</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
    <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="2">
        <Table xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:DefaultColumnWidth="125">
            <Column ss:AutoFitWidth="1"/>
            <Row xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">Audit ID</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">Audit Subcategory ID</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">2</Data>
                </Cell>
            </Row>
            <Row xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">105076</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">10</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">2</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
    <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="3">
        <Table xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:DefaultColumnWidth="125">
            <Column ss:AutoFitWidth="1"/>
            <Row xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">Audit ID</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">Audit Subcategory ID</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">3</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
    <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="4">
        <Table xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:DefaultColumnWidth="125">
            <Column ss:AutoFitWidth="1"/>
            <Row xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">Audit ID</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">Audit Subcategory ID</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s62">
                    <Data ss:Type="String">4</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
</Workbook>

@RossPresser , Thanks for asking. I have modified the question with sample xml. — Nimble Fungus
– Nimble Fungus, Commented Dec 10, 2015 at 17:34

Josh Part · Accepted Answer · 2015-12-10 18:26:31Z

2

The problem is that here

insert attribute  ss:HRef {"#''sql:variable(""@worksheets"")''!A1"}

it seems as if you expect xquery to "magically" concatenate those strings. You need to specify it. The fact that you needed to put extra quotes and apostrophes implies that it is considering it as a single string.

Your insert should look like this

set @xml.modify('declare default element namespace  "urn:schemas-microsoft-com:office:spreadsheet";
                declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
                declare namespace x="urn:schemas-microsoft-com:office:excel";
                insert attribute ss:HRef {concat("#", sql:variable("@worksheets") cast as xs:string?, "!A1")}
                into (/Workbook/Worksheet[1]/Table/Row[sql:variable("@worksheets")]/Cell)[1]');

I'm assuming that @worksheets is a numeric variable; that's the reason for the cast as xs:string.

answered Dec 10, 2015 at 18:26

Josh Part

2,16412 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nimble Fungus Over a year ago

Thanks, this was the correct answer. That was the mistake i was doing.

Collectives™ on Stack Overflow

Issue with XML Modify

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related