Extract XML data from MS SQL table and import to 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:


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=InternationalTrade;"


'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$2")
End Sub






Here is an example of the unformatted and Pretty printed XML


<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>






<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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,114,068
Messages
5,545,800
Members
410,708
Latest member
SanTrapGamer
Top