新增文章

文章标题分类内容

DROP TABLE #XMLTab

CREATE TABLE #XMLTab(colxml XML)

INSERT INTO #XMLTab 
   SELECT N'<Record id="1629">  <Exception type="RING_BUFFER_EXCEPTION">    <Task address="0x0062B8E8" />    <Error>9003</Error>    <Severity>error</Severity>  </Exception>  <Exception type="BUFFER_EXCEPTION">    <Task address="0xC452BB39" />    <Error>8008</Error>    <Severity>true</Severity>  </Exception></Record>'
   
   INSERT INTO #XMLTab 
   SELECT N'<Record id="1611">  <Exception type="RING_BUFFER_EXCEPTION">    <Task address="" />    <Error>7803</Error>    <Severity>false</Severity>  </Exception>  <Exception type="BUFFER">    <Task address="0xC4445674" />    <Error>4300</Error>    <Severity>right</Severity>  </Exception></Record>'
   
   SELECT colxml FROM #XMLTab

SELECT 
    node.c1.value('(@id)[1]','VARCHAR(50)') AS [id] 
   ,node.c1.value('(Exception/@type)[1]','VARCHAR(50)') AS [type1] 
   ,node.c1.value('(Exception/Task/@address)[1]','VARCHAR(50)') AS [address1] 
   ,node.c1.value('(Exception/Error)[1]','VARCHAR(max)') AS [Error1] 
   ,node.c1.value('(Exception/Severity)[1]','VARCHAR(max)') AS [Severity1] 
   ,node.c1.value('(Exception/@type)[2]','VARCHAR(50)') AS [type2] 
   ,node.c1.value('(Exception/Task/@address)[2]','VARCHAR(50)') AS [address2] 
   ,node.c1.value('(Exception/Error)[2]','VARCHAR(max)') AS [Error2] 
   ,node.c1.value('(Exception/Severity)[2]','VARCHAR(max)') AS [Severity2] 
   
   FROM #XMLTab 
   
   CROSS APPLY colxml.nodes('/Record') as node(c1)