当前位置: 动力学知识库 > 问答 > 编程问答 >

Dynamic XML node name in SQL XML

问题描述:

I am updating an XML column with values from columns in a temp table. I can update the table as below.

UPDATE tbWorkflow

SET xmlData.modify('insert

(<FromQueueName>

<CustomerID>{ sql:column("T.iVTollCustID") }</CustomerID>

<Date>{ sql:variable("@CurrDateTime") }</Date>

</FromQueueName>)

as first into (/configuration)[1]'),

vcQueue = 'qVtoll',

dtUpdTime = GETDATE()

FROM #Trxns T

WHERE T.biWorkflowID = tbWorkflow.biWorkflowID

However, I want the node name to be dynamic (from the temp table) like below. But it does not work.

UPDATE tbWorkflow

SET xmlData.modify('insert

(<**{ sql:column("T.vcQueue") }**>

<CustomerID>{ sql:column("T.iVTollCustID") }</CustomerID>

<Date>{ sql:variable("@CurrDateTime") }</Date>

</**{ sql:column("T.vcQueue") }**>)

as first into (/configuration)[1]'),

vcQueue = 'qVtoll',

dtUpdTime = GETDATE()

FROM #Trxns T

WHERE T.biWorkflowID = tbWorkflow.biWorkflowID

Any help is appreciated.

网友答案:

I think, you should use a subquery to select a string you need and then insert it into XML. For example, in subquery get a <YourTag><CustomerID>123</CustomerID><Date>15.10.2012</Date></YourTag> and then you can simply insert it in XML

网友答案:

I found a workaround to do this.

I added another VARCHAR column to my temp table, created the xml node as a varchar and later modified my actual xml column using this varchar column.

UPDATE  #Trxns
SET     xmlVarchar = '<' + vcQueue + '>
        <CustomerID>' + CONVERT(VARCHAR(10),iVTollCustID) + '</CustomerID>
        <Date>' + CONVERT(VARCHAR(25), GETDATE(),22) + '</Date>' +
        '</' + vcQueue + '>'


UPDATE  tbWorkflow 
SET     xmlData.modify('insert 
                sql:column("xmlVarchar")
                as first into (/configuration)[1]'),
        vcQueue             =   'qVtoll',
        dtUpdTime           =   GETDATE()
FROM    #Trxns    T  
WHERE   T.biWorkflowID      =   tbWorkflow.biWorkflowID 
分享给朋友:
您可能感兴趣的文章:
随机阅读: