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