Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: MS SQL to clean XML

    Good day

    I need help extracting the values from the XML below in TSQL into a table.

    What I need, all as one record is
    <HostRef>Specto_15936580</HostRef>

    And all the
    <Result name="xxx" value="xxxx" />
    Where the “name=” is to be the column heading and the value the “value=” of the record

    The XML looks like (XML attached , myxml.txt, just rename file to myxml.xml)

    <TSAMessage>
    <BusinessInterface>Test</BusinessInterface>
    <BusinessOperation>PSETestResultNotify</BusinessOperation>
    <MessageId>483d697f</MessageId>
    <InternalMessageId>5bbe381d-e978-4793-95a0-9df0bc51cbfc</InternalMessageId>
    <CorrelationId>483d697f</CorrelationId>
    <SenderId>TMII</SenderId>
    <GeneratedTimeStamp>2012-01-23T13:33:22.935+02:00</GeneratedTimeStamp>
    <Message>
    <PSETestResult>
    <Request>
    <TestRequestID>23073103</TestRequestID>
    <TestRequestStatus>COMPLETE</TestRequestStatus>
    <ReportedContext>
    <HostID>FAMC</HostID>
    <HostRef>Specto_15936580</HostRef>
    </ReportedContext>
    <TestRequestResult>
    <TestRequestID>23073103</TestRequestID>
    <TestResultCode>Worker</TestResultCode>
    <TestResultDate>2012-01-23T13:31:21.000+02:00</TestResultDate>
    <TestType>NFOPortal</TestType>
    <TestResultSpecialisation>00</TestResultSpecialisation>
    <Comment>XACT:Modem Syncing
    XACT: Downlink Speed = 1023000bps
    SHDSL:Modem Syncing
    LINE: Test OK: Vercode 00</Comment>
    </TestRequestResult>
    <PercentageDegrade>0.12</PercentageDegrade>
    <LocalizationResult>
    <SegmentEntity>
    <EntityID>0235411658</EntityID>
    <NicotraAlarmInd>N</NicotraAlarmInd>
    </SegmentEntity>
    <TestResults>
    <TestSet>
    <Category>TM Summary</Category>
    <Result name="Line_Status" value="Vercode 00 - Test OK" />
    <Result name="TM_Reason" value="xAct syncing and Line test ok" />
    <Result name="xAct_Status" value="Modem Syncing" />
    <Result name="TM_Action" value="Confirm service is restored with Customer" />
    <Result name="TM_Status" value="Pass" />
    </TestSet>
    <TestSet>
    <Category>xAct Results</Category>
    <Result name="DSLAM_STATION_NAME" value="Prince Albert IMAX" />
    <Result name="ATUC_CURRENT_ATN" value="8.6" />
    <Result name="DSLAM_Port_Number" value="13" />
    <Result name="ATUR_CURRENT_SNR" value="36.6 - Pass" />
    <Result name="ATUC_ATTAINABLE_KBPS" value="21876" />
    <Result name="PROFILE_NFO" value="ADSLupto1024af01" />
    <Result name="DSLAM_Slot_Number" value="12" />
    <Result name="ATUC_CHAN_CURRENT_TX_RATE" value="1023" />
    <Result name="AdslAturCurrOutputPower" value="-22" />
    <Result name="hwPortOperStatus" value="Activated" />
    <Result name="ATUC_CURRENT_SNR" value="29" />
    <Result name="ATUR_CURRENT_ATN" value="21.1 - Pass" />
    <Result name="OperStatus" value="1" />
    <Result name="AdslModulationType" value="MultiMode" />
    <Result name="AdslAtucCurrOutputPower" value="35" />
    <Result name="ATUR_CHAN_CURRENT_TX_RATE" value="507" />
    <Result name="ATUR_ATTAINABLE_KBPS" value="1224" />
    </TestSet>
    <TestSet>
    <Category>Line Results</Category>
    <Result name="AC_Voltage.AB" value="0.00" />
    <Result name="LDT" value="N/A" />
    <Result name="AC_Voltage.AE" value="0.00" />
    <Result name="DC_Voltage.BE" value="0.00" />
    <Result name="TestType" value="LOOP" />
    <Result name="Dispatch1" value="Dispatch: NO DISPATCH" />
    <Result name="DC_Voltage.AE" value="0.00" />
    <Result name="CPE.AB" value="YES" />
    <Result name="Impedance.AB" value="12.8" />
    <Result name="Noise.Earth" value="48.7" />
    <Result name="Resistance.BE" value="9999" />
    <Result name="VerCode" value="00" />
    <Result name="VerificationMsg" value="TEST OK" />
    <Result name="DC_Voltage.AB" value="0.00" />
    <Result name="Resistance.AB" value="2284" />
    <Result name="AC_Voltage.BE" value="0.00" />
    <Result name="Noise.Metallic" value="11.9" />
    <Result name="NLT" value="PASS" />
    <Result name="Resistance.AE" value="9999" />
    <Result name="Length" value="1.11 KM" />
    <Result name="Termination" value="TERMINATED" />
    <Result name="Impedance.AE" value="4629" />
    <Result name="Impedance.BE" value="9999" />
    <Result name="Capacitance.AB" value="2.68" />
    </TestSet>
    </TestResults>
    </LocalizationResult>
    </Request>
    </PSETestResult>
    </Message>
    </TSAMessage>'
    Attached Files Attached Files
    Last edited by MR_X; 01-24-12 at 07:36.

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: MS SQL to clean XML

    You may want to consider using dynamic SQL with a PIVOT, as follows:

    Code:
    declare @xd xml;
    set @xd = N'<TSAMessage>
      <BusinessInterface>Test</BusinessInterface>
      <BusinessOperation>PSETestResultNotify</BusinessOperation>
      <MessageId>483d697f</MessageId>
      <InternalMessageId>5bbe381d-e978-4793-95a0-9df0bc51cbfc</InternalMessageId>
      <CorrelationId>483d697f</CorrelationId>
      <SenderId>TMII</SenderId>
      <GeneratedTimeStamp>2012-01-23T13:33:22.935+02:00</GeneratedTimeStamp>
      <Message>
        <PSETestResult>
          <Request>
            <TestRequestID>23073103</TestRequestID>
            <TestRequestStatus>COMPLETE</TestRequestStatus>
            <ReportedContext>
              <HostID>FAMC</HostID>
              <HostRef>Specto_15936580</HostRef>
            </ReportedContext>
            <TestRequestResult>
              <TestRequestID>23073103</TestRequestID>
              <TestResultCode>Worker</TestResultCode>
              <TestResultDate>2012-01-23T13:31:21.000+02:00</TestResultDate>
              <TestType>NFOPortal</TestType>
              <TestResultSpecialisation>00</TestResultSpecialisation>
              <Comment>XACT:Modem Syncing
    XACT: Downlink Speed = 1023000bps
    SHDSL:Modem Syncing
    LINE: Test OK: Vercode 00</Comment>
            </TestRequestResult>
            <PercentageDegrade>0.12</PercentageDegrade>
            <LocalizationResult>
              <SegmentEntity>
                <EntityID>0235411658</EntityID>
                <NicotraAlarmInd>N</NicotraAlarmInd>
              </SegmentEntity>
              <TestResults>
                <TestSet>
                  <Category>TM Summary</Category>
                  <Result name="Line_Status" value="Vercode 00 - Test OK" />
                  <Result name="TM_Reason" value="xAct syncing and Line test ok" />
                  <Result name="xAct_Status" value="Modem Syncing" />
                  <Result name="TM_Action" value="Confirm service is restored with Customer" />
                  <Result name="TM_Status" value="Pass" />
                </TestSet>
                <TestSet>
                  <Category>xAct Results</Category>
                  <Result name="DSLAM_STATION_NAME" value="Prince Albert IMAX" />
                  <Result name="ATUC_CURRENT_ATN" value="8.6" />
                  <Result name="DSLAM_Port_Number" value="13" />
                  <Result name="ATUR_CURRENT_SNR" value="36.6 - Pass" />
                  <Result name="ATUC_ATTAINABLE_KBPS" value="21876" />
                  <Result name="PROFILE_NFO" value="ADSLupto1024af01" />
                  <Result name="DSLAM_Slot_Number" value="12" />
                  <Result name="ATUC_CHAN_CURRENT_TX_RATE" value="1023" />
                  <Result name="AdslAturCurrOutputPower" value="-22" />
                  <Result name="hwPortOperStatus" value="Activated" />
                  <Result name="ATUC_CURRENT_SNR" value="29" />
                  <Result name="ATUR_CURRENT_ATN" value="21.1 - Pass" />
                  <Result name="OperStatus" value="1" />
                  <Result name="AdslModulationType" value="MultiMode" />
                  <Result name="AdslAtucCurrOutputPower" value="35" />
                  <Result name="ATUR_CHAN_CURRENT_TX_RATE" value="507" />
                  <Result name="ATUR_ATTAINABLE_KBPS" value="1224" />
                </TestSet>
                <TestSet>
                  <Category>Line Results</Category>
                  <Result name="AC_Voltage.AB" value="0.00" />
                  <Result name="LDT" value="N/A" />
                  <Result name="AC_Voltage.AE" value="0.00" />
                  <Result name="DC_Voltage.BE" value="0.00" />
                  <Result name="TestType" value="LOOP" />
                  <Result name="Dispatch1" value="Dispatch: NO DISPATCH" />
                  <Result name="DC_Voltage.AE" value="0.00" />
                  <Result name="CPE.AB" value="YES" />
                  <Result name="Impedance.AB" value="12.8" />
                  <Result name="Noise.Earth" value="48.7" />
                  <Result name="Resistance.BE" value="9999" />
                  <Result name="VerCode" value="00" />
                  <Result name="VerificationMsg" value="TEST OK" />
                  <Result name="DC_Voltage.AB" value="0.00" />
                  <Result name="Resistance.AB" value="2284" />
                  <Result name="AC_Voltage.BE" value="0.00" />
                  <Result name="Noise.Metallic" value="11.9" />
                  <Result name="NLT" value="PASS" />
                  <Result name="Resistance.AE" value="9999" />
                  <Result name="Length" value="1.11 KM" />
                  <Result name="Termination" value="TERMINATED" />
                  <Result name="Impedance.AE" value="4629" />
                  <Result name="Impedance.BE" value="9999" />
                  <Result name="Capacitance.AB" value="2.68" />
                </TestSet>
              </TestResults>
            </LocalizationResult>
          </Request>
        </PSETestResult>
      </Message>
    </TSAMessage>'
    
    declare @pvf nvarchar(max) ,@sql nvarchar(max);
    
    set @pvf = stuff((
    select ' ,[' + rs.value('@name[1]','sysname') + ']'
    from @xd.nodes('//Result') R(RS)
    for xml path('')),1,2,'');
    
    set @sql = N'select * from (
    select
      rc.value(''HostRef[1]'',''sysname'') as HostRef
     ,rs.value(''@name[1]'',''sysname'') as RName
     ,rs.value(''@value[1]'',''sysname'') as RValue
    from @xdoc.nodes(''//ReportedContext'') X(RC)
    outer apply @xdoc.nodes(''//Result'') R(RS)
    ) t
    pivot (MIN(RValue) for RName in (' + @pvf + ')) p'
    
    exec sp_executesql @sql ,N'@xdoc xml' ,@xdoc = @xd
    Last edited by homerow; 01-24-12 at 13:06. Reason: Took out Category field

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •