比较麻烦的SQL处理XML!

Question

DocumentID,                  MsgType,Comments,TotalSystem,ValidSystem, ServiceTag, SystemError
9S12800717039360004730Y487G42 1      FAILED   10          6            2CRDMH1    00144470635776..
.....
其它的数据只有ServiceTag, SystemError不同，取XML的值
一共4条

Answer 1

应该不是很难，可是我现在对XML的处理还是不怎么行。

Answer 2

DECLARE @doc XML;
SET @doc='
<QDS_ACK>
  <SENDERID>O2_Q_EMEA</SENDERID>
  <RECEIVERID>QCI</RECEIVERID>
  <DOCUMENTID>9S12800717039360004730Y487G42</DOCUMENTID>
  <MSG_TYPE>1</MSG_TYPE>
  <REPORT>
    <SUMMARY>
      <ACK_ACTION>R</ACK_ACTION>
      <COMMENTS>FAILED : INVALID SVCTAG DETAILS</COMMENTS>
      <TOTAL_SYSTEMS>10</TOTAL_SYSTEMS>
      <VALID_SYSTEMS>6</VALID_SYSTEMS>
      <INVALID_SYSTEMS>4</INVALID_SYSTEMS>
    </SUMMARY>
    <DETAIL>
      <SYSTEM_MSG>
        <SERVICE_TAG>2CRDMH1</SERVICE_TAG>
        <SYSTEM_ERRORS>
          <SYSTEM_ERROR>00144470635776 : DUPLICATE COASTRING ACROSS ASBUILTS</SYSTEM_ERROR>
        </SYSTEM_ERRORS>
      </SYSTEM_MSG>
      <SYSTEM_MSG>
        <SERVICE_TAG>G8RDMH1</SERVICE_TAG>
        <SYSTEM_ERRORS>
          <SYSTEM_ERROR>00144470635773 : DUPLICATE COASTRING ACROSS ASBUILTS</SYSTEM_ERROR>
        </SYSTEM_ERRORS>
      </SYSTEM_MSG>
      <SYSTEM_MSG>
        <SERVICE_TAG>4BRDMH1</SERVICE_TAG>
        <SYSTEM_ERRORS>
          <SYSTEM_ERROR>00144470635497 : DUPLICATE COASTRING ACROSS ASBUILTS</SYSTEM_ERROR>
        </SYSTEM_ERRORS>
      </SYSTEM_MSG>
      <SYSTEM_MSG>
        <SERVICE_TAG>H7RDMH1</SERVICE_TAG>
        <SYSTEM_ERRORS>
          <SYSTEM_ERROR>00144470635936 : DUPLICATE COASTRING ACROSS ASBUILTS</SYSTEM_ERROR>
        </SYSTEM_ERRORS>
      </SYSTEM_MSG>
    </DETAIL>
  </REPORT>
</QDS_ACK>
'SELECT
    A.x.value('(./../../../DOCUMENTID)[1]','varchar(20)') AS DocumentID,
    A.x.value('(./../../../MSG_TYPE)[1]','varchar(10)') AS MSG_TYPE,
    A.x.value('(./../../SUMMARY/COMMENTS)[1]','varchar(20)') AS COMMENTS,
    A.x.value('(./../../SUMMARY/TOTAL_SYSTEMS)[1]','varchar(20)') AS TOTAL_SYSTEMS,
    A.x.value('(./../../SUMMARY/VALID_SYSTEMS)[1]','varchar(20)') AS VALID_SYSTEMS,
    A.x.value('(./SERVICE_TAG)[1]','varchar(20)') AS SERVICE_TAG,
    A.x.value('(./SYSTEM_ERRORS/SYSTEM_ERROR)[1]','varchar(20)') AS SYSTEM_ERROR
FROM @doc.nodes('//SYSTEM_MSG') AS A(x)/*
DocumentID           MSG_TYPE   COMMENTS             TOTAL_SYSTEMS        VALID_SYSTEMS        SERVICE_TAG          SYSTEM_ERROR
-------------------- ---------- -------------------- -------------------- -------------------- -------------------- --------------------
9S128007170393600047 1          FAILED : INVALID SVC 10                   6                    2CRDMH1              00144470635776 : DUP
9S128007170393600047 1          FAILED : INVALID SVC 10                   6                    G8RDMH1              00144470635773 : DUP
9S128007170393600047 1          FAILED : INVALID SVC 10                   6                    4BRDMH1              00144470635497 : DUP
9S128007170393600047 1          FAILED : INVALID SVC 10                   6                    H7RDMH1              00144470635936 : DUP(4 行受影响)
*/

Answer 3

DECLARE @doc XML;
SET @doc='
<QDS_ACK>
  <SENDERID>O2_Q_EMEA</SENDERID>
  <RECEIVERID>QCI</RECEIVERID>
  <DOCUMENTID>9S12800717039360004730Y487G42</DOCUMENTID>
  <MSG_TYPE>1</MSG_TYPE>
  <REPORT>
    <SUMMARY>
      <ACK_ACTION>R</ACK_ACTION>
      <COMMENTS>FAILED : INVALID SVCTAG DETAILS</COMMENTS>
      <TOTAL_SYSTEMS>10</TOTAL_SYSTEMS>
      <VALID_SYSTEMS>6</VALID_SYSTEMS>
      <INVALID_SYSTEMS>4</INVALID_SYSTEMS>
    </SUMMARY>
    <DETAIL>
      <SYSTEM_MSG>
        <SERVICE_TAG>2CRDMH1</SERVICE_TAG>
        <SYSTEM_ERRORS>
          <SYSTEM_ERROR>00144470635776 : DUPLICATE COASTRING ACROSS ASBUILTS</SYSTEM_ERROR>
        </SYSTEM_ERRORS>
      </SYSTEM_MSG>
      <SYSTEM_MSG>
        <SERVICE_TAG>G8RDMH1</SERVICE_TAG>
        <SYSTEM_ERRORS>
          <SYSTEM_ERROR>00144470635773 : DUPLICATE COASTRING ACROSS ASBUILTS</SYSTEM_ERROR>
        </SYSTEM_ERRORS>
      </SYSTEM_MSG>
      <SYSTEM_MSG>
        <SERVICE_TAG>4BRDMH1</SERVICE_TAG>
        <SYSTEM_ERRORS>
          <SYSTEM_ERROR>00144470635497 : DUPLICATE COASTRING ACROSS ASBUILTS</SYSTEM_ERROR>
        </SYSTEM_ERRORS>
      </SYSTEM_MSG>
      <SYSTEM_MSG>
        <SERVICE_TAG>H7RDMH1</SERVICE_TAG>
        <SYSTEM_ERRORS>
          <SYSTEM_ERROR>00144470635936 : DUPLICATE COASTRING ACROSS ASBUILTS</SYSTEM_ERROR>
        </SYSTEM_ERRORS>
      </SYSTEM_MSG>
    </DETAIL>
  </REPORT>
</QDS_ACK>
';WITH Liang AS 
(
    SELECT
        A.x.value('(./../../../DOCUMENTID)[1]','varchar(50)') AS DocumentID,
        A.x.value('(./../../../MSG_TYPE)[1]','int') AS MSG_TYPE,
        A.x.value('(./../../SUMMARY/COMMENTS)[1]','varchar(50)') AS COMMENTS,
        A.x.value('(./../../SUMMARY/TOTAL_SYSTEMS)[1]','int') AS TOTAL_SYSTEMS,
        A.x.value('(./../../SUMMARY/VALID_SYSTEMS)[1]','int') AS VALID_SYSTEMS,
        A.x.value('(./SERVICE_TAG)[1]','varchar(50)') AS SERVICE_TAG,
        A.x.value('(./SYSTEM_ERRORS/SYSTEM_ERROR)[1]','varchar(50)') AS SYSTEM_ERROR
    FROM @doc.nodes('//SYSTEM_MSG') AS A(x)
)
SELECT 
    DocumentID,
    MSG_TYPE,
    RTRIM(LEFT(COMMENTS,CHARINDEX(':',COMMENTS)-1)) AS COMMENTS,
    TOTAL_SYSTEMS,
    VALID_SYSTEMS,
    SERVICE_TAG,
    RTRIM(LEFT(SYSTEM_ERROR,CHARINDEX(':',SYSTEM_ERROR)-1)) AS SYSTEM_ERROR
FROM Liang/*
DocumentID                                         MSG_TYPE    COMMENTS                                           TOTAL_SYSTEMS VALID_SYSTEMS SERVICE_TAG                                        SYSTEM_ERROR
-------------------------------------------------- ----------- -------------------------------------------------- ------------- ------------- -------------------------------------------------- --------------------------------------------------
9S12800717039360004730Y487G42                      1           FAILED                                             10            6             2CRDMH1                                            00144470635776
9S12800717039360004730Y487G42                      1           FAILED                                             10            6             G8RDMH1                                            00144470635773
9S12800717039360004730Y487G42                      1           FAILED                                             10            6             4BRDMH1                                            00144470635497
9S12800717039360004730Y487G42                      1           FAILED                                             10            6             H7RDMH1                                            00144470635936(4 行受影响)
*/

Answer 4

A.x.value('(./../../../DOCUMENTID)[1]','varchar(50)') 小梁这个是什么意思啊？

Answer 5

找父的父的父的DOCUMENTID节点呀.

Answer 6

保证只返回一个值,
因为xml的value方法需要确保只返回一个值,否则将产生静态错误.

Answer 7

小梁兄
@doc.nodes('//SYSTEM_MSG')  为什么用两个斜杠？

Answer 8

为什么不是从跟节点<QDS_ACK> 开始？

Answer 9

应该可以从<QDS_ACK> 开始吧？

Answer 10

如果从跟开始写是这样的吗？
SELECT
    A.x.value('(QDS_ACK/DOCUMENTID)[1]','varchar(20)') AS DocumentID
FROM @doc.nodes('QDS_ACK') AS A(x)
可是为什么是空值啊？

Answer 11

如果从跟开始写是这样的吗？
SELECT
    A.x.value('(QDS_ACK/DOCUMENTID)[1]','varchar(20)') AS DocumentID
FROM @doc.nodes('QDS_ACK') AS A(x)
可是为什么是空值啊？

Answer 12

FROM @doc.nodes('/QDS_ACK/REPORT/DETAIL/SYSTEM_MSG') AS A(x)