zzl437

SQL读取XML并根据需要增加节点(带有命名空间)

DECLARE @ItemMessage XML,@NodeExist BIT

SET @ItemMessage=

'<?xml version="1.0" encoding="UTF-8"?>

<vCheckTester OperatorNumber="zhjy" OperationNumber="operation" WorkOrderNumber="WO" EquipmentNumber="Equipment" xmlns="Valor.vCheckTester.xsd" Version="4.0" xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">

<Unit xmlns="Valor.vCheckTester.xsd" Sequence="1" PanelStatusCode="FAIL" PanelSerialNumber="PIDTEST0521001" IsUnitInsidePanel="true" StatusCode="PASS" Timestamp="2012-05-21T11:04:03">

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Inrush Current_240V_50Hz_1A" ValueUnit="Double" UpperLimit="15" LowerLimit="2" MeasurementUnit="A" Value="12.391" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Turn on time_110V_47Hz_1A" ValueUnit="Double" UpperLimit="3" LowerLimit="0" MeasurementUnit="s" Value="0.166" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Output Rise time_110V_47Hz_1A" ValueUnit="Double" UpperLimit="3" LowerLimit="0.001" MeasurementUnit="s" Value="0.023" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Overshoot at Start Up_110V_47Hz_1A" ValueUnit="Double" UpperLimit="5.25" LowerLimit="5" MeasurementUnit="V" Value="5.140" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Static Test_110V_47Hz_1A" ValueUnit="Double" UpperLimit="5.245" LowerLimit="5.005" MeasurementUnit="V" Value="5.095" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Protection O.C.P_230V_50Hz_1A" ValueUnit="Double" UpperLimit="1.5" LowerLimit="1" MeasurementUnit="A" Value="1.350" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Static Test_90V_47Hz_1A" ValueUnit="Double" UpperLimit="5.245" LowerLimit="5.005" MeasurementUnit="V" Value="5.096" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Protection OVP_264V_50Hz_1A" ValueUnit="Double" UpperLimit="6.3" LowerLimit="5.45" MeasurementUnit="V" Value="5.530" DateTime="2012-05-21T11:04:03"/>

<Header TestFixtureNumber="F87651" TestHeadNumber="614A-H4123"/>

</Unit>

<Unit xmlns="Valor.vCheckTester.xsd" Sequence="2" PanelStatusCode="FAIL" PanelSerialNumber="PIDTEST0521001" IsUnitInsidePanel="true" StatusCode="PASS" Timestamp="2012-05-21T11:04:03">

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Inrush Current_240V_50Hz_1A" ValueUnit="Double" UpperLimit="15" LowerLimit="2" MeasurementUnit="A" Value="12.391" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Turn on time_110V_47Hz_1A" ValueUnit="Double" UpperLimit="3" LowerLimit="0" MeasurementUnit="s" Value="0.166" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Output Rise time_110V_47Hz_1A" ValueUnit="Double" UpperLimit="3" LowerLimit="0.001" MeasurementUnit="s" Value="0.023" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Overshoot at Start Up_110V_47Hz_1A" ValueUnit="Double" UpperLimit="5.25" LowerLimit="5" MeasurementUnit="V" Value="5.140" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Static Test_110V_47Hz_1A" ValueUnit="Double" UpperLimit="5.245" LowerLimit="5.005" MeasurementUnit="V" Value="5.095" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Protection O.C.P_230V_50Hz_1A" ValueUnit="Double" UpperLimit="1.5" LowerLimit="1" MeasurementUnit="A" Value="1.350" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Static Test_90V_47Hz_1A" ValueUnit="Double" UpperLimit="5.245" LowerLimit="5.005" MeasurementUnit="V" Value="5.096" DateTime="2012-05-21T11:04:03"/>

<Measurement xmlns="Valor.vCheckTester.xsd" StatusCode="PASS" Name="Protection OVP_264V_50Hz_1A" ValueUnit="Double" UpperLimit="6.3" LowerLimit="5.45" MeasurementUnit="V" Value="5.530" DateTime="2012-05-21T11:04:03"/>

<Header TestFixtureNumber="F87651" TestHeadNumber="614A-H4123"/>

</Unit>

</vCheckTester>'

;WITH XMLNAMESPACES(DEFAULT 'Valor.vCheckTester.xsd') 

SELECT @NodeExist=@ItemMessage.exist('/vCheckTester/Unit/Header')

if @NodeExist=0

SET @ItemMessage.modify('

declare default element namespace "Valor.vCheckTester.xsd";

insert <Header xmlns="Valor.vCheckTester.xsd" /> as last into (vCheckTester/Unit)[1] ')

;WITH XMLNAMESPACES(DEFAULT 'Valor.vCheckTester.xsd') 

SELECT DISTINCT

T1.A.value('@OperatorNumber','NVARCHAR(300)') OperatorNumber,

T1.A.value('@OperationNumber','NVARCHAR(300)') OperationNumber,

T1.A.value('@WorkOrderNumber','varchar(200)') WorkOrderNumber,

T1.A.value('@EquipmentNumber','varchar(200)') EquipmentNumber,

T2.A.value('@PanelSerialNumber','NVARCHAR(300)') PanelSerialNumber,

T2.A.value('@PanelStatusCode','NVARCHAR(300)') PanelStatusCode,

T2.A.value('@Sequence','int') [Sequence],

T2.A.value('@SerialNumber','varchar(200)') SerialNumber,

T2.A.value('@IsUnitInsidePanel','bit') IsUnitInsidePanel,

T2.A.value('@StatusCode','NVARCHAR(300)') UnitStatusCode,

T2.A.value('@Timestamp','datetime') [Timestamp],

T3.A.value('@Name','NVARCHAR(300)') TestItemName,

T3.A.value('@StatusCode','NVARCHAR(300)') TestItemStatusCode,

T3.A.value('@ValueUnit','NVARCHAR(300)') ValueUnit,

T3.A.value('@UpperLimit','float') UpperLimit,

T3.A.value('@LowerLimit','float') LowerLimit,

T3.A.value('@MeasurementUnit','NVARCHAR(300)') MeasurementUnit,

T3.A.value('@DateTime','NVARCHAR(300)') [DateTime],

T4.A.value('@TestFixtureNumber','NVARCHAR(300)') TestFixtureNumber,

T4.A.value('@TestHeadNumber','NVARCHAR(300)') TestHeadNumber

FROM @ItemMessage.nodes('/vCheckTester') AS T1(A),@ItemMessage.nodes('/vCheckTester/Unit') AS T2(A),@ItemMessage.nodes('/vCheckTester/Unit/Measurement') AS T3(A),@ItemMessage.nodes('/vCheckTester/Unit/Header') AS T4(A)

ORDER BY [Sequence],TestItemName