Question

I have a stored proc that inserts XML into an underlying table. Unfortunately the XML passed in as a Sql XML type sometimes has particular elements missing. This makes the INSERT statement insert XML node values into the wrong columns.

My T-SQL looks something like this:

ALTER PROCEDURE [dbo].[up_Test_Insert]
    @xmlData XML
AS
BEGIN
INSERT INTO
TestTable
(
  ColumnA,
  ColumnB,
  ColumnC
)
SELECT
    Tbl.Col.value(ColumnA[1] ,  INT ),
    Tbl.Col.value( ColumnB[1] ,  INT ),
    Tbl.Col.value( ColumnC[1] ,  INT )
 FROM
   @xmlData.nodes( DocumentElement/DataTable ) Tbl(Col)

有时是哥伦堡 XML结构中可能缺少B部分。结果是,将哥伦堡市价 no升值列入ColumnB表栏。

是否有办法使“价值”经营者的返回对失踪分子无效?

Answer 1

你可能把样本简化得太大。您在此所做的工作是预期的,不会插入哥伦。哥伦卡表上的C值和哥伦布角的缺失将使你失去价值。

为此:

declare @xmlData xml =  
<DocumentElement>
  <DataTable>
    <ColumnA>1</ColumnA>
    <ColumnC>3</ColumnC>
  </DataTable>
</DocumentElement>
 

SELECT
    Tbl.Col.value( ColumnA[1] ,  INT ),
    Tbl.Col.value( ColumnB[1] ,  INT ),
    Tbl.Col.value( ColumnC[1] ,  INT )
 FROM
   @xmlData.nodes( DocumentElement/DataTable ) Tbl(Col)

结果:

----------- ----------- -----------
1           NULL        3

友情链接