Extract XML data from MS SQL and Import into Excel

MattW1976

New Member
Joined
Apr 13, 2015
Messages
4
I have unformatted XML data in one column of a MS SQL server database. I want to extract it and reformat it so I can import it into excel. What is the best way to do this? Right now, I'm trying to extract it to a file and then import it but it isn't working. I have to extract it to a file, open it in notepad++ and using the XML tools add-in to format it using Pretty print (XML - only with linebreaks) and then it imports just fine. I'd like to be able to just pass it through a function that reformats the XML that way and import it without using a file at all. Here is the code I have so far:


Code:
Sub DataExtractToFile()


Dim fFile As Long
Dim strFile As String


strFile = Environ("temp") & "\ugly.xml"
If FileThere(strFile) Then Kill strFile
strFile = Environ("temp") & "\ugly.xml"


fFile = FreeFile


' Create a connection object.
Dim cnITrade As ADODB.Connection
Set cnITrade = New ADODB.Connection


' Provide the connection string.
Dim strConn As String


'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"


'Connect to the InternationalTrade database on the local server.
strConn = strConn & "DATA SOURCE=.\SQLExpress;INITIAL CATALOG=ITrade;"


'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"


'Now open the connection.
cnITrade.Open strConn


' Create a recordset object.
Dim rsITrade As ADODB.Recordset
Set rsITrade = New ADODB.Recordset


With rsITrade
  ' Assign the Connection object.
  .ActiveConnection = cnITrade
  ' Extract the required records.
  .Open "SELECT xml_msg FROM TABLE with (nolock) where xml_msg like '%ABC01156%'"
          Open strFile For Output As #fFile
          Print #fFile, .GetString(adClipString, 1, "><", vbCrLf, "")
  ' Tidy up
   Close #fFile
  .Close
End With


cnITrade.Close
Set rsITrade = Nothing
Set cnITrade = Nothing


End Sub


Function FileThere(FileName As String) As Boolean
     FileThere = (Dir(FileName) > "")
End Function




Sub ImportXML(sPath As String)


    ActiveWorkbook.XmlImportXml sPath, ImportMap:= _
        Nothing, Overwrite:=True, Destination:=Range("$A$1")
End Sub




Here is an example of the unformatted and Pretty printed XML


Code:
<Proponix><Header><DestinationID>ABC</DestinationID><SenderID>PRO</SenderID><ClientBank>123</ClientBank><OperationOrganizationID>ABC1</OperationOrganizationID><MessageType>MSG</MessageType><DateSent>20100104</DateSent><TimeSent>161601</TimeSent><MessageID>0000007522</MessageID></Header><SubHeader><InstrumentID>ABC123456</InstrumentID><InstrumentStatus>ABC</InstrumentStatus><ActivityType>CUS</ActivityType><ActivityStatus>REL</ActivityStatus><BusinessDate>20100104</BusinessDate><OriginalActivityType>CUS</OriginalActivityType><RelatedInstrumentID>AB1241550001</RelatedInstrumentID><RelatedActivitySequenceNo>1</RelatedActivitySequenceNo><ProductType>OUT</ProductType><Product>TAC</Product><ProductCategory>US</ProductCategory><RelationshipCustomerID>DESI0101156</RelationshipCustomerID><LimitCustomerID>DESI0101156</LimitCustomerID><BaseCurrency>USD</BaseCurrency><InstrumentCurrency>USD</InstrumentCurrency><LimitCurrency>USD</LimitCurrency><MessageSequenceNumber>000000000020106</MessageSequenceNumber></SubHeader><Body><Activity><DateActivity>20091230</DateActivity><ActivitySequenceNo>0</ActivitySequenceNo><SequenceDate>20100104</SequenceDate><SequenceTime>161559</SequenceTime><ActivityAmount>120506.00</ActivityAmount></Activity><ResultingTerms><InstrumentTerms><DateStart>20091230</DateStart><DateEnd>20100318</DateEnd><ReissuanceInd>N</ReissuanceInd><ConvertedInd>N</ConvertedInd><CountryRisk>US</CountryRisk><TenorCategory>T</TenorCategory></InstrumentTerms><UsanceTerms><DispositionType>HELD</DispositionType><UnconfirmedInd>Y</UnconfirmedInd><NumberOfDays>78</NumberOfDays><TenorDetailType>AFM</TenorDetailType></UsanceTerms></ResultingTerms><Parties><Party><PartyType>DWR</PartyType><CustomerID>ABCI0101156</CustomerID><ReferenceNo>04-COMPUTER-09</ReferenceNo></Body></Proponix>




Code:
<Proponix>
	<Header>
		<DestinationID>ABC</DestinationID>
		<SenderID>PRO</SenderID>
		<ClientBank>123</ClientBank>
		<OperationOrganizationID>ABC1</OperationOrganizationID>
		<MessageType>MSG</MessageType>
		<DateSent>20100104</DateSent>
		<TimeSent>161601</TimeSent>
		<MessageID>0000007522</MessageID>
	</Header>
	<SubHeader>
		<InstrumentID>ABC123456</InstrumentID>
		<InstrumentStatus>ABC</InstrumentStatus>
		<ActivityType>CUS</ActivityType>
		<ActivityStatus>REL</ActivityStatus>
		<BusinessDate>20100104</BusinessDate>
		<OriginalActivityType>CUS</OriginalActivityType>
		<RelatedInstrumentID>AB1241550001</RelatedInstrumentID>
		<RelatedActivitySequenceNo>1</RelatedActivitySequenceNo>
		<ProductType>OUT</ProductType>
		<Product>TAC</Product>
		<ProductCategory>US</ProductCategory>
		<RelationshipCustomerID>DESI0101156</RelationshipCustomerID>
		<LimitCustomerID>DESI0101156</LimitCustomerID>
		<BaseCurrency>USD</BaseCurrency>
		<InstrumentCurrency>USD</InstrumentCurrency>
		<LimitCurrency>USD</LimitCurrency>
		<MessageSequenceNumber>000000000020106</MessageSequenceNumber>
	</SubHeader>
	<Body>
		<Activity>
			<DateActivity>20091230</DateActivity>
			<ActivitySequenceNo>0</ActivitySequenceNo>
			<SequenceDate>20100104</SequenceDate>
			<SequenceTime>161559</SequenceTime>
			<ActivityAmount>120506.00</ActivityAmount>
		</Activity>
		<ResultingTerms>
			<InstrumentTerms>
				<DateStart>20091230</DateStart>
				<DateEnd>20100318</DateEnd>
				<ReissuanceInd>N</ReissuanceInd>
				<ConvertedInd>N</ConvertedInd>
				<CountryRisk>US</CountryRisk>
				<TenorCategory>T</TenorCategory>
			</InstrumentTerms>
			<UsanceTerms>
				<DispositionType>HELD</DispositionType>
				<UnconfirmedInd>Y</UnconfirmedInd>
				<NumberOfDays>78</NumberOfDays>
				<TenorDetailType>AFM</TenorDetailType>
			</UsanceTerms>
		</ResultingTerms>
		<Parties>
			<Party>
				<PartyType>DWR</PartyType>
				<CustomerID>ABCI0101156</CustomerID>
				<ReferenceNo>04-COMPUTER-09</ReferenceNo>
                       </Party>			
        </Body>
</Proponix>
 
Last edited:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Watch MrExcel Video

Forum statistics

Threads
1,113,836
Messages
5,544,596
Members
410,623
Latest member
RusHartley
Top