sql 学习笔记

sql server如何将xml格式转换成表

DECLARE @ResultXml XML = '<DocumentElement>
    <dt>
        <CustomerCode>B77674</CustomerCode>
        <CustomerName>郑州日产汽车有限公司9999</CustomerName>
        <EnableMark>1</EnableMark>
        <ModifyDate>2017-10-25 06:09:02</ModifyDate>
    </dt>
    <dt>
        <CustomerCode>B776749</CustomerCode>
        <CustomerName>郑州日产汽车有限公司12398</CustomerName>
        <EnableMark>0</EnableMark>
        <ModifyDate>2017-10-25 06:09:03</ModifyDate>
    </dt>
    </DocumentElement>'

SELECT  aw.row.value('CustomerCode[1]', 'VARCHAR(50)') AS CustomerCode ,
        aw.row.value('CustomerName[1]', 'VARCHAR(50)') AS CustomerName ,
        aw.row.value('EnableMark[1]', 'BIT') AS EnableMark ,
        aw.row.value('ModifyDate[1]', 'DATETIME') AS ModifyDate
FROM    @ResultXml.nodes('/DocumentElement/dt') AS aw ( row );

MERGE语法的运用

MERGE INTO dbo.Customer AS customer
            --#tmpCustomer 数据源
                    USING 
                        ( SELECT    *
                          FROM      #tmpCustomer
                        ) AS tmp
                    ON customer.CustomerCode = tmp.CustomerCode
                    WHEN MATCHED 
                        THEN UPDATE
                         SET        customer.CustomerName = tmp.CustomerName ,
                                    customer.ModifyDate = tmp.ModifyDate ,
                                    customer.ModifyUserId = @CreateUserId
                    WHEN NOT MATCHED 
                        THEN
                            INSERT  (
                                      CustomerId ,
                                      CustomerCode ,
                                      CustomerName ,
                                      EnabledMark ,
                                      CreateDate ,
                                      CreateUserId ,
                                      OrganizeId
                                    )
                         VALUES     ( NEWID() ,
                                      tmp.CustomerCode ,
                                      tmp.CustomerName ,
                                      tmp.EnableMark ,
                                      tmp.ModifyDate ,
                                      @CreateUserId ,
                                      @OrganizeId
                                    );