Vision XML Stored in the database

As many of us know, The vision application stores xml strings in the database as way to record parameters and settings. Many times, these strings are tens of thousands of characters long and are hard to read from a sql client tool interface.In addition, the strings sometimes exceed the character count limitation that many SQL client tools impose and you cannot fully view the contents of the xml.

To help parse out the xml in a usable format, I've written the query (no guarantees with no liabilities) below as an example of which parses the report name and the where clause out of the report log table and displays it.

select ReportLog.Options.query( N'/options/reportName/text()') as ReportName,
ReportLog.Options.query( N'/options/where/text()') as WhereClause
from
(
select Convert(XML,Options) as Options from reportlog
) as ReportLog


For those of you who are working within a Multicurrency environment, you can also query (no guarantees with no liabilities) the ledger tables to parse out information about rates and dates used on Given transactions



select LedgerAR.wbs1,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgRate/text()') as ExchgRate,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgDate/text()') as ExchgDate,
LedgerAR.ExchangeInfo.query( N'/parms/OvrDateUsed/text()') as OvrDateUsed,
LedgerAR.ExchangeInfo.query( N'/parms/OvrAmtUsed/text()') as OvrAmtUsed,
LedgerAR.ExchangeInfo.query( N'/parms/InvUsed/text()') as InvUsed,
LedgerAR.ExchangeInfo.query( N'/parms/TriUsed/text()') as TriUsed,
LedgerAR.ExchangeInfo.query( N'/parms/TriCurCode/text()') as TriCurCode,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgRate2/text()') as ExchgRate2,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgDate2/text()') as ExchgDate2,
LedgerAR.ExchangeInfo.query( N'/parms/InvUsed2/text()') as InvUsed2

FROM
( select wbs1,
Convert(xml,ExchangeInfo) as ExchangeInfo
from LedgerAR
where ExchangeInfo is not null
) as LedgerAR

The above queries (no guarantees with no liabilities) should help you get started in dealing with long xml strings stored in the vision database.

Enjoy......