Temenos Transact RDBMS Views and Indices

This section provides the appendices pertaining to the RDBMS views and indices.

Appendix A- CUSTOMER@XMLREF

The following example shows an XMLREF record generated from Standard Selection.

NOTE: This was generated at the time of this document and may not necessarily reflect your own version.
MNEMONIC]1]Mnemonic]0]0]L]S]c1]10]]c1]0]0]MNEMONIC
SHORT.NAME]2]ShortName]2]0]L]S]c2]35]gShortName]c2]0]0]SHORT_NAME
NAME.1]3]Name1]3]0]L]S]c3]35]gName1]c3]0]0]NAME_1
NAME.2]4]Name2]4]0]L]S]c4]35]gName2]c4]0]0]NAME_2
STREET]5]Street]5]0]L]S]c5]35]gStreet]c5]0]0]STREET
TOWN.COUNTRY]6]TownCountry]6]0]L]S]c6]35]gTownCountry]c6]0]0]TOWN_COUNTRY
RELATION.CODE]7]RelationCode]7]0]R]S]c7]3]gRelationCode/mRelationCode]c7]1]0]RELATION_CODE
REL.CUSTOMER]8]RelCustomer]7]0]R]N]c8]10]gRelationCode/mRelationCode]c8]0]0]REL_CUSTOMER
REVERS.REL.CODE]9]ReversRelCode]7]0]R]S]c9]60]gRelationCode/mRelationCode]c9]0]0]REVERS_REL_CODE
SECTOR]10]Sector]0]0]R]S]c10]4]]c10]0]0]SECTOR
ACCOUNT.OFFICER]11]AccountOfficer]0]0]R]S]c11]4]]c11]0]0]ACCOUNT_OFFICER
OTHER.OFFICER]12]OtherOfficer]12]0]R]S]c12]4]gOtherOfficer]c12]0]0]OTHER_OFFICER
INDUSTRY]13]Industry]0]0]R]S]c13]4]]c13]0]0]INDUSTRY
TARGET]14]Target]0]0]R]S]c14]4]]c14]0]0]TARGET
NATIONALITY]15]Nationality]0]0]L]S]c15]2]]c15]0]0]NATIONALITY
CUSTOMER.STATUS]16]CustomerStatus]0]0]R]S]c16]4]]c16]0]0]CUSTOMER_STATUS
RESIDENCE]17]Residence]0]0]L]S]c17]2]]c17]0]0]RESIDENCE
CONTACT.DATE]18]ContactDate]0]0]R]D]c18]11]]c18]0]0]CONTACT_DATE
INTRODUCER]19]Introducer]0]0]L]S]c19]35]]c19]0]0]INTRODUCER
TEXT]20]Text]20]0]L]S]c20]35]gText]c20]0]0]TEXT
LEGAL.ID]21]LegalId]0]0]L]S]c21]20]]c21]0]0]LEGAL_ID
REVIEW.FREQUENCY]22]ReviewFrequency]0]0]L]S]c22]17]]c22]0]0]REVIEW_FREQUENCY
BIRTH.INCORP.DATE]23]BirthIncorpDate]0]0]R]D]c23]11]]c23]0]0]BIRTH_INCORP_DATE
GLOBAL.CUSTOMER]24]GlobalCustomer]0]0]R]S]c24]10]]c24]0]0]GLOBAL_CUSTOMER
CUSTOMER.LIABILITY]25]CustomerLiability]0]0]R]N]c25]10]]c25]0]0]CUSTOMER_LIABILITY
LANGUAGE]26]Language]0]0]R]S]c26]2]]c26]0]0]LANGUAGE
POSTING.RESTRICT]27]PostingRestrict]0]0]R]S]c27]2]]c27]0]0]POSTING_RESTRICT
DISPO.OFFICER]28]DispoOfficer]0]0]R]S]c28]4]]c28]0]0]DISPO_OFFICER
POST.CODE]29]PostCode]29]0]L]S]c29]35]gPostCode]c29]0]0]POST_CODE
COUNTRY]30]Country]30]0]L]S]c30]35]gCountry]c30]0]0]COUNTRY
COMPANY.BOOK]31]CompanyBook]0]0]L]S]c31]11]]c31]0]0]COMPANY_BOOK
CONFID.TXT]32]ConfidTxt]0]0]R]S]c32]3]]c32]0]0]CONFID_TXT
DISPO.EXEMPT]33]DispoExempt]0]0]R]S]c33]3]]c33]0]0]DISPO_EXEMPT
ISSUE.CHEQUES]34]IssueCheques]0]0]R]S]c34]3]]c34]0]0]ISSUE_CHEQUES
CLS.CPARTY]35]ClsCparty]0]0]R]S]c35]3]]c35]0]0]CLS_CPARTY
FX.COMM.GROUP.ID]36]FxCommGroupid]0]0]R]S]c36]3]]c36]0]0]FX_COMM_GROUP_ID
RESIDENCE.REGION]37]ResidenceRegion]0]0]L]S]c37]9]]c37]0]0]RESIDENCE_REGION
ASSET.CLASS]38]AssetClass]0]0]L]S]c38]60]]c38]0]0]ASSET_CLASS
CUSTOMER.RATING]39]CustomerRating]39]0]L]S]c39]10]gCustomerRating]c39]0]0]CUSTOMER_RATING
LOCAL.REF]40]LocalRef]40]0]L]S]c40]35]gLocalRef]c40]0]0]LOCAL_REF
OVERRIDE]41]Override]41]0]L]S]c41]60]gOverride]c41]0]0]OVERRIDE
RECORD.STATUS]42]RecordStatus]0]0]L]S]c42]4]]c42]0]0]RECORD_STATUS
CURR.NO]43]CurrNo]0]0]R]S]c43]4]]c43]0]0]CURR_NO
INPUTTER]44]Inputter]44]0]L]S]c44]35]gInputter]c44]0]0]INPUTTER
DATE.TIME]45]DateTime]45]0]R]S]c45]15]gDateTime]c45]0]0]DATE_TIME
AUTHORISER]46]Authoriser]0]0]L]S]c46]20]]c46]0]0]AUTHORISER
CO.CODE]47]CoCode]0]0]L]S]c47]11]]c47]0]0]CO_CODE
DEPT.CODE]48]DeptCode]0]0]L]S]c48]4]]c48]0]0]DEPT_CODE
AUDITOR.CODE]49]AuditorCode]0]0]L]S]c49]16]]c49]0]0]AUDITOR_CODE
AUDIT.DATE.TIME]50]AuditDateTime]0]0]R]S]c50]15]]c50]0]0]AUDIT_DATE_TIME
SIGNATURE.Y.N]-1]SignatureYN]40]0]L]S]c40]1]gLocalRef/LocalRef[1]]c40[1]]0]]LOCAL_REF
CAPITAL]-1]Capital]40]0]R]N]c40]20]gLocalRef/LocalRef[2]]c40[2]]0]]LOCAL_REF
GB.FGN.RESIDENT]-1]GbFgnResident]40]0]L]S]c40]3]gLocalRef/LocalRef[4]]c40[4]]0]]LOCAL_REF
RELIGION]-1]Religion]40]0]L]S]c40]15]gLocalRef/LocalRef[5]]c40[5]]0]]LOCAL_REF
PREF.CLIENT]-1]PrefClient]40]0]L]S]c40]16]gLocalRef/LocalRef[6]]c40[6]]0]]LOCAL_REF
SEC.TYPE.TRADE]-1]SecTypeTrade]40]0]L]S]c40]4]gLocalRef/LocalRef[1]]c40[1]]0]]LOCAL_REF
SEC.TYP.MGT.FEE]-1]SecTypMgtfee]40]0]L]S]c40]4]gLocalRef/LocalRef[2]]c40[2]]0]]LOCAL_REF
FID.DEPOSIT.REF]-1]FidDepositRef]40]0]R]S]c40]12]gLocalRef/LocalRef[4]]c40[4]]0]]LOCAL_REF
SEC.TYPE.SAFEKP]-1]SecTypeSafekp]40]0]L]S]c40]4]gLocalRef/LocalRef[3]]c40[3]]0]]LOCAL_REF
ALT.ID.CUST]-1]AltIdCust]40]0]L]S]c40]60]gLocalRef/LocalRef[5]]c40[5]]0]]LOCAL_REF

The following table lists the legends of multi-value fields in XMLREF.

Field Names

Description

XF_fldName

Indicates the pick field name

XF_fldNbr

Indicates the field number

XF_tagName

Indicates the XML element name

XF_ctrlAMFldNbr

Indicates whether AM or 0 is controlled

XF_ctrlMVFldNbr

Indicates whether MV or 0 is controlled

XF_fldJust

Indicates the R or L justification

XF_xrefType

Indicates the type N,S,D

XF_cfldNbr

Indicates the Short tag

XF_Length

Indicates the field length

XF_xpathExpr

Indicates the long parent tag for groups

XF_shorttag

Indicates the Short tag

XF_MVctrlFlag

Indicates that it is a controlling field in multi-field multivalue-group

XF_SMVctrlFlag

Indicates that it is a controlling field in multi-field subvalue-group

XF_SQLcolumn

Indicates the SQL column name

Appendix B- Short Tag View Example

The following is an example of a CREATE-VIEW on a CUSTOMER record in various databases.

ClosedDB2 
$ create-view -v CUSTOMER
Script name = XMLView_V_CUSTOMER.sql
Script = DROP VIEW V_CUSTOMER
/
CREATE VIEW V_CUSTOMER
(RECID,XMLRECORD,MNEMONIC,nMNEMONIC,SECTOR,nSECTOR,ACCOUNT_OFFICER,nACCOUNT_OFFICER,INDUSTRY,nINDUSTRY,TARGET,nTARGET,NATIONALITY,nNATIONALITY,CUSTOMER_STATUS,nCUSTOMER_STATUS,RESIDENCE,nRESIDENCE,CONTACT_DATE,nCONTACT_DATE,INTRODUCER,nINTRODUCER,LEGAL_ID,nLEGAL_ID,REVIEW_FREQUENCY,nREVIEW_FREQUENCY,BIRTH_INCORP_DATE,nBIRTH_INCORP_DATE,GLOBAL_CUSTOMER,nGLOBAL_CUSTOMER,CUSTOMER_LIABILITY,nCUSTOMER_LIABILITY,LANGUAGE,nLANGUAGE,POSTING_RESTRICT,nPOSTING_RESTRICT,DISPO_OFFICER,nDISPO_OFFICER,COMPANY_BOOK,nCOMPANY_BOOK,CONFID_TXT,nCONFID_TXT,DISPO_EXEMPT,nDISPO_EXEMPT,ISSUE_CHEQUES,nISSUE_CHEQUES,CLS_CPARTY,nCLS_CPARTY,FX_COMM_GROUP_ID,nFX_COMM_GROUP_ID,RESIDENCE_REGION,nRESIDENCE_REGION,ASSET_CLASS,nASSET_CLASS,SIGNATURE_Y_N,nSIGNATURE_Y_N,SEC_TYPE_TRADE,nSEC_TYPE_TRADE,OPEN_DATE,nOPEN_DATE,FID_DEPOSIT_REF,nFID_DEPOSIT_REF,CUS_DEAL_SLIP,nCUS_DEAL_SLIP,SEC_TYP_MGT_FEE,nSEC_TYP_MGT_FEE,RELIGION,nRELIGION,ALT_ID_CUST,nALT_ID_CUST,CAPITAL,nCAPITAL,SEC_TYPE_SAFEKP,nSEC_TYPE_SAFEKP,GB_FGN_RESIDENT,nGB_FGN_RESIDENT,PREF_CLIENT,nPREF_CLIENT,RECORD_STATUS,nRECORD_STATUS,CURR_NO,nCURR_NO,AUTHORISER,nAUTHORISER,CO_CODE,nCO_CODE,DEPT_CODE,nDEPT_CODE,AUDITOR_CODE,nAUDITOR_CODE,AUDIT_DATE_TIME,nAUDIT_DATE_TIME)
 AS SELECT x.RECID,x.XMLRECORD
,t.MNEMONIC,t.nMNEMONIC,t.SECTOR,t.nSECTOR,t.ACCOUNT_OFFICER,t.nACCOUNT_OFFICER,t.INDUSTRY,t.nINDUSTRY,t.TARGET,t.nTARGET,t.NATIONALITY,t.nNATIONALITY,t.CUSTOMER_STATUS,t.nCUSTOMER_STATUS,t.RESIDENCE,t.nRESIDENCE,t.CONTACT_DATE,t.nCONTACT_DATE,t.INTRODUCER,t.nINTRODUCER,t.LEGAL_ID,t.nLEGAL_ID,t.REVIEW_FREQUENCY,t.nREVIEW_FREQUENCY,t.BIRTH_INCORP_DATE,t.nBIRTH_INCORP_DATE,t.GLOBAL_CUSTOMER,t.nGLOBAL_CUSTOMER,t.CUSTOMER_LIABILITY,t.nCUSTOMER_LIABILITY,t.LANGUAGE,t.nLANGUAGE,t.POSTING_RESTRICT,t.nPOSTING_RESTRICT,t.DISPO_OFFICER,t.nDISPO_OFFICER,t.COMPANY_BOOK,t.nCOMPANY_BOOK,t.CONFID_TXT,t.nCONFID_TXT,t.DISPO_EXEMPT,t.nDISPO_EXEMPT,t.ISSUE_CHEQUES,t.nISSUE_CHEQUES,t.CLS_CPARTY,t.nCLS_CPARTY,t.FX_COMM_GROUP_ID,t.nFX_COMM_GROUP_ID,t.RESIDENCE_REGION,t.nRESIDENCE_REGION,t.ASSET_CLASS,t.nASSET_CLASS,t.SIGNATURE_Y_N,t.nSIGNATURE_Y_N,t.SEC_TYPE_TRADE,t.nSEC_TYPE_TRADE,t.OPEN_DATE,t.nOPEN_DATE,t.FID_DEPOSIT_REF,t.nFID_DEPOSIT_REF,t.CUS_DEAL_SLIP,t.nCUS_DEAL_SLIP,t.SEC_TYP_MGT_FEE,t.nSEC_TYP_MGT_FEE,t.RELIGION,t.nRELIGION,t.ALT_ID_CUST,t.nALT_ID_CUST,t.CAPITAL,t.nCAPITAL,t.SEC_TYPE_SAFEKP,t.nSEC_TYPE_SAFEKP,t.GB_FGN_RESIDENT,t.nGB_FGN_RESIDENT,t.PREF_CLIENT,t.nPREF_CLIENT,t.RECORD_STATUS,t.nRECORD_STATUS,t.CURR_NO,t.nCURR_NO,t.AUTHORISER,t.nAUTHORISER,t.CO_CODE,t.nCO_CODE,t.DEPT_CODE,t.nDEPT_CODE,t.AUDITOR_CODE,t.nAUDITOR_CODE,t.AUDIT_DATE_TIME,t.nAUDIT_DATE_TIME
FROM CUSTOMER x, xmltable('$t/row' passing x.XMLRECORD as "t" columns
MNEMONIC VARCHAR(10) Path 'c1'
,nMNEMONIC DOUBLE Path 'c1'
,SECTOR VARCHAR(4) Path 'c10'
,nSECTOR DOUBLE Path 'c10'
,ACCOUNT_OFFICER VARCHAR(4) Path 'c11'
,nACCOUNT_OFFICER DOUBLE Path 'c11'
,INDUSTRY VARCHAR(4) Path 'c13'
,nINDUSTRY DOUBLE Path 'c13'
,TARGET VARCHAR(4) Path 'c14'
,nTARGET DOUBLE Path 'c14'
,NATIONALITY VARCHAR(2) Path 'c15'
,nNATIONALITY DOUBLE Path 'c15'
,CUSTOMER_STATUS VARCHAR(4) Path 'c16'
,nCUSTOMER_STATUS DOUBLE Path 'c16'
,RESIDENCE VARCHAR(2) Path 'c17'
,nRESIDENCE DOUBLE Path 'c17'
,CONTACT_DATE VARCHAR(11) Path 'c18'
,nCONTACT_DATE DOUBLE Path 'c18'
,INTRODUCER VARCHAR(35) Path 'c19'
,nINTRODUCER DOUBLE Path 'c19'
,LEGAL_ID VARCHAR(20) Path 'c21'
,nLEGAL_ID DOUBLE Path 'c21'
,REVIEW_FREQUENCY VARCHAR(17) Path 'c22'
,nREVIEW_FREQUENCY DOUBLE Path 'c22'
,BIRTH_INCORP_DATE VARCHAR(11) Path 'c23'
,nBIRTH_INCORP_DATE DOUBLE Path 'c23'
,GLOBAL_CUSTOMER VARCHAR(10) Path 'c24'
,nGLOBAL_CUSTOMER DOUBLE Path 'c24'
,CUSTOMER_LIABILITY VARCHAR(10) Path 'c25'
,nCUSTOMER_LIABILITY DOUBLE Path 'c25'
,LANGUAGE VARCHAR(2) Path 'c26'
,nLANGUAGE DOUBLE Path 'c26'
,POSTING_RESTRICT VARCHAR(2) Path 'c27'
,nPOSTING_RESTRICT DOUBLE Path 'c27'
,DISPO_OFFICER VARCHAR(4) Path 'c28'
,nDISPO_OFFICER DOUBLE Path 'c28'
,COMPANY_BOOK VARCHAR(11) Path 'c31'
,nCOMPANY_BOOK DOUBLE Path 'c31'
,CONFID_TXT VARCHAR(3) Path 'c32'
,nCONFID_TXT DOUBLE Path 'c32'
,DISPO_EXEMPT VARCHAR(3) Path 'c33'
,nDISPO_EXEMPT DOUBLE Path 'c33'
,ISSUE_CHEQUES VARCHAR(3) Path 'c34'
,nISSUE_CHEQUES DOUBLE Path 'c34'
,CLS_CPARTY VARCHAR(3) Path 'c35'
,nCLS_CPARTY DOUBLE Path 'c35'
,FX_COMM_GROUP_ID VARCHAR(3) Path 'c36'
,nFX_COMM_GROUP_ID DOUBLE Path 'c36'
,RESIDENCE_REGION VARCHAR(9) Path 'c37'
,nRESIDENCE_REGION DOUBLE Path 'c37'
,ASSET_CLASS VARCHAR(3) Path 'c38'
,nASSET_CLASS DOUBLE Path 'c38'
,SIGNATURE_Y_N VARCHAR(1) Path 'c40[1]'
,nSIGNATURE_Y_N DOUBLE Path 'c40[1]'
,SEC_TYPE_TRADE VARCHAR(4) Path 'c40[1]'
,nSEC_TYPE_TRADE DOUBLE Path 'c40[1]'
,OPEN_DATE VARCHAR(9) Path 'c40[@m=3]'
,nOPEN_DATE DOUBLE Path 'c40[@m=3]'
,FID_DEPOSIT_REF VARCHAR(12) Path 'c40[@m=4]'
,nFID_DEPOSIT_REF DOUBLE Path 'c40[@m=4]'
,CUS_DEAL_SLIP VARCHAR(10) Path 'c40[@m=6]'
,nCUS_DEAL_SLIP DOUBLE Path 'c40[@m=6]'
,SEC_TYP_MGT_FEE VARCHAR(4) Path 'c40[@m=2]'
,nSEC_TYP_MGT_FEE DOUBLE Path 'c40[@m=2]'
,RELIGION VARCHAR(15) Path 'c40[@m=5]'
,nRELIGION DOUBLE Path 'c40[@m=5]'
,ALT_ID_CUST VARCHAR(7) Path 'c40[@m=5]'
,nALT_ID_CUST DOUBLE Path 'c40[@m=5]'
,CAPITAL VARCHAR(20) Path 'c40[@m=2]'
,nCAPITAL DOUBLE Path 'c40[@m=2]'
,SEC_TYPE_SAFEKP VARCHAR(4) Path 'c40[@m=3]'
,nSEC_TYPE_SAFEKP DOUBLE Path 'c40[@m=3]'
,GB_FGN_RESIDENT VARCHAR(3) Path 'c40[@m=4]'
,nGB_FGN_RESIDENT DOUBLE Path 'c40[@m=4]'
,PREF_CLIENT VARCHAR(16) Path 'c40[@m=6]'
,nPREF_CLIENT DOUBLE Path 'c40[@m=6]'
,RECORD_STATUS VARCHAR(4) Path 'c42'
,nRECORD_STATUS DOUBLE Path 'c42'
,CURR_NO VARCHAR(4) Path 'c43'
,nCURR_NO DOUBLE Path 'c43'
,AUTHORISER VARCHAR(20) Path 'c46'
,nAUTHORISER DOUBLE Path 'c46'
,CO_CODE VARCHAR(11) Path 'c47'
,nCO_CODE DOUBLE Path 'c47'
,DEPT_CODE VARCHAR(4) Path 'c48'
,nDEPT_CODE DOUBLE Path 'c48'
,AUDITOR_CODE VARCHAR(16) Path 'c49'
,nAUDITOR_CODE DOUBLE Path 'c49'
,AUDIT_DATE_TIME VARCHAR(15) Path 'c50'
,nAUDIT_DATE_TIME DOUBLE Path 'c50'
) as t
/
ClosedOracle 
$ create-view -v CUSTOMER
Script name = XMLView_V_CUSTOMER.sql
Now processing
==============
CREATE OR REPLACE VIEW V_CUSTOMER
(RECID,XMLRECORD,MNEMONIC,nMNEMONIC,SECTOR,nSECTOR,ACCOUNT_OFFICER,nACCOUNT_OFFI
CER,INDUSTRY,nINDUSTRY,TARGET,nTARGET,NATIONALITY,nNATIONALITY,CUSTOMER_STATUS,n
CUSTOMER_STATUS,RESIDENCE,nRESIDENCE,CONTACT_DATE,nCONTACT_DATE,INTRODUCER,nINTR
ODUCER,LEGAL_ID,nLEGAL_ID,REVIEW_FREQUENCY,nREVIEW_FREQUENCY,BIRTH_INCORP_DATE,n
BIRTH_INCORP_DATE,GLOBAL_CUSTOMER,nGLOBAL_CUSTOMER,CUSTOMER_LIABILITY,nCUSTOMER_
LIABILITY,LANGUAGE,nLANGUAGE,POSTING_RESTRICT,nPOSTING_RESTRICT,DISPO_OFFICER,nD
ISPO_OFFICER,COMPANY_BOOK,nCOMPANY_BOOK,CONFID_TXT,nCONFID_TXT,DISPO_EXEMPT,nDIS
PO_EXEMPT,ISSUE_CHEQUES,nISSUE_CHEQUES,CLS_CPARTY,nCLS_CPARTY,FX_COMM_GROUP_ID,n
FX_COMM_GROUP_ID,RESIDENCE_REGION,nRESIDENCE_REGION,ASSET_CLASS,nASSET_CLASS,SIG
NATURE_Y_N,nSIGNATURE_Y_N,SEC_TYPE_TRADE,nSEC_TYPE_TRADE,SEC_TYPE_SAFEKP,nSEC_TY
PE_SAFEKP,SEC_TYP_MGT_FEE,nSEC_TYP_MGT_FEE,RELIGION,nRELIGION,PREF_CLIENT,nPREF_
CLIENT,OPEN_DATE,nOPEN_DATE,GB_FGN_RESIDENT,nGB_FGN_RESIDENT,FID_DEPOSIT_REF,nFI
D_DEPOSIT_REF,CUS_DEAL_SLIP,nCUS_DEAL_SLIP,CAPITAL,nCAPITAL,ALT_ID_CUST,nALT_ID_
CUST,RECORD_STATUS,nRECORD_STATUS,CURR_NO,nCURR_NO,AUTHORISER,nAUTHORISER,CO_COD
E,nCO_CODE,DEPT_CODE,nDEPT_CODE,AUDITOR_CODE,nAUDITOR_CODE,AUDIT_DATE_TIME,nAUDI
T_DATE_TIME)
 AS SELECT x.RECID, x.XMLRECORD.getClobVal() "XMLRECORD"
,SUBSTR(extractValue(x.xmlrecord,'/row/c1'),1,10) "MNEMONIC",NUMCAST(extractValu
e(x.xmlrecord,'/row/c1')) "nMNEMONIC",SUBSTR(extractValue(x.xmlrecord,'/row/c10'
),1,4) "SECTOR",NUMCAST(extractValue(x.xmlrecord,'/row/c10')) "nSECTOR",SUBSTR(e
xtractValue(x.xmlrecord,'/row/c11'),1,4) "ACCOUNT_OFFICER",NUMCAST(extractValue(
x.xmlrecord,'/row/c11')) "nACCOUNT_OFFICER",SUBSTR(extractValue(x.xmlrecord,'/ro
w/c13'),1,4) "INDUSTRY",NUMCAST(extractValue(x.xmlrecord,'/row/c13')) "nINDUSTRY
",SUBSTR(extractValue(x.xmlrecord,'/row/c14'),1,4) "TARGET",NUMCAST(extractValue
(x.xmlrecord,'/row/c14')) "nTARGET",SUBSTR(extractValue(x.xmlrecord,'/row/c15'),
1,2) "NATIONALITY",NUMCAST(extractValue(x.xmlrecord,'/row/c15')) "nNATIONALITY",
SUBSTR(extractValue(x.xmlrecord,'/row/c16'),1,4) "CUSTOMER_STATUS",NUMCAST(extra
ctValue(x.xmlrecord,'/row/c16')) "nCUSTOMER_STATUS",SUBSTR(extractValue(x.xmlrec
ord,'/row/c17'),1,2) "RESIDENCE",NUMCAST(extractValue(x.xmlrecord,'/row/c17')) "
nRESIDENCE",SUBSTR(extractValue(x.xmlrecord,'/row/c18'),1,11) "CONTACT_DATE",NUM
CAST(extractValue(x.xmlrecord,'/row/c18')) "nCONTACT_DATE",SUBSTR(extractValue(x
.xmlrecord,'/row/c19'),1,35) "INTRODUCER",NUMCAST(extractValue(x.xmlrecord,'/row
/c19')) "nINTRODUCER",SUBSTR(extractValue(x.xmlrecord,'/row/c21'),1,20) "LEGAL_I
D",NUMCAST(extractValue(x.xmlrecord,'/row/c21')) "nLEGAL_ID",SUBSTR(extractValue
(x.xmlrecord,'/row/c22'),1,17) "REVIEW_FREQUENCY",NUMCAST(extractValue(x.xmlreco
rd,'/row/c22')) "nREVIEW_FREQUENCY",SUBSTR(extractValue(x.xmlrecord,'/row/c23'),
1,11) "BIRTH_INCORP_DATE",NUMCAST(extractValue(x.xmlrecord,'/row/c23')) "nBIRTH_
INCORP_DATE",SUBSTR(extractValue(x.xmlrecord,'/row/c24'),1,10) "GLOBAL_CUSTOMER"
,NUMCAST(extractValue(x.xmlrecord,'/row/c24')) "nGLOBAL_CUSTOMER",SUBSTR(extract
Value(x.xmlrecord,'/row/c25'),1,10) "CUSTOMER_LIABILITY",NUMCAST(extractValue(x.
xmlrecord,'/row/c25')) "nCUSTOMER_LIABILITY",SUBSTR(extractValue(x.xmlrecord,'/r
ow/c26'),1,2) "LANGUAGE",NUMCAST(extractValue(x.xmlrecord,'/row/c26')) "nLANGUAG
E",SUBSTR(extractValue(x.xmlrecord,'/row/c27'),1,2) "POSTING_RESTRICT",NUMCAST(e
xtractValue(x.xmlrecord,'/row/c27')) "nPOSTING_RESTRICT",SUBSTR(extractValue(x.x
mlrecord,'/row/c28'),1,4) "DISPO_OFFICER",NUMCAST(extractValue(x.xmlrecord,'/row
/c28')) "nDISPO_OFFICER",SUBSTR(extractValue(x.xmlrecord,'/row/c31'),1,11) "COMP
ANY_BOOK",NUMCAST(extractValue(x.xmlrecord,'/row/c31')) "nCOMPANY_BOOK",SUBSTR(e
xtractValue(x.xmlrecord,'/row/c32'),1,3) "CONFID_TXT",NUMCAST(extractValue(x.xml
record,'/row/c32')) "nCONFID_TXT",SUBSTR(extractValue(x.xmlrecord,'/row/c33'),1,
3) "DISPO_EXEMPT",NUMCAST(extractValue(x.xmlrecord,'/row/c33')) "nDISPO_EXEMPT",
SUBSTR(extractValue(x.xmlrecord,'/row/c34'),1,3) "ISSUE_CHEQUES",NUMCAST(extract
Value(x.xmlrecord,'/row/c34')) "nISSUE_CHEQUES",SUBSTR(extractValue(x.xmlrecord,
'/row/c35'),1,3) "CLS_CPARTY",NUMCAST(extractValue(x.xmlrecord,'/row/c35')) "nCL
S_CPARTY",SUBSTR(extractValue(x.xmlrecord,'/row/c36'),1,3) "FX_COMM_GROUP_ID",NU
MCAST(extractValue(x.xmlrecord,'/row/c36')) "nFX_COMM_GROUP_ID",SUBSTR(extractVa
lue(x.xmlrecord,'/row/c37'),1,9) "RESIDENCE_REGION",NUMCAST(extractValue(x.xmlre
cord,'/row/c37')) "nRESIDENCE_REGION",SUBSTR(extractValue(x.xmlrecord,'/row/c38'
),1,3) "ASSET_CLASS",NUMCAST(extractValue(x.xmlrecord,'/row/c38')) "nASSET_CLASS
",SUBSTR(extractValue(x.xmlrecord,'/row/c40[1]'),1,1) "SIGNATURE_Y_N",NUMCAST(ex
tractValue(x.xmlrecord,'/row/c40[1]')) "nSIGNATURE_Y_N",SUBSTR(extractValue(x.xm
lrecord,'/row/c40[1]'),1,4) "SEC_TYPE_TRADE",NUMCAST(extractValue(x.xmlrecord,'/
row/c40[1]')) "nSEC_TYPE_TRADE",SUBSTR(extractValue(x.xmlrecord,'/row/c40[@m="3"
]'),1,4) "SEC_TYPE_SAFEKP",NUMCAST(extractValue(x.xmlrecord,'/row/c40[@m="3"]'))
 "nSEC_TYPE_SAFEKP",SUBSTR(extractValue(x.xmlrecord,'/row/c40[@m="2"]'),1,4) "SE
C_TYP_MGT_FEE",NUMCAST(extractValue(x.xmlrecord,'/row/c40[@m="2"]')) "nSEC_TYP_M
GT_FEE",SUBSTR(extractValue(x.xmlrecord,'/row/c40[@m="5"]'),1,15) "RELIGION",NUM
CAST(extractValue(x.xmlrecord,'/row/c40[@m="5"]')) "nRELIGION",SUBSTR(extractVal
ue(x.xmlrecord,'/row/c40[@m="6"]'),1,16) "PREF_CLIENT",NUMCAST(extractValue(x.xm
lrecord,'/row/c40[@m="6"]')) "nPREF_CLIENT",SUBSTR(extractValue(x.xmlrecord,'/ro
w/c40[@m="3"]'),1,9) "OPEN_DATE",NUMCAST(extractValue(x.xmlrecord,'/row/c40[@m="
3"]')) "nOPEN_DATE",SUBSTR(extractValue(x.xmlrecord,'/row/c40[@m="4"]'),1,3) "GB
_FGN_RESIDENT",NUMCAST(extractValue(x.xmlrecord,'/row/c40[@m="4"]')) "nGB_FGN_RE
SIDENT",SUBSTR(extractValue(x.xmlrecord,'/row/c40[@m="4"]'),1,12) "FID_DEPOSIT_R
EF",NUMCAST(extractValue(x.xmlrecord,'/row/c40[@m="4"]')) "nFID_DEPOSIT_REF",SUB
STR(extractValue(x.xmlrecord,'/row/c40[@m="6"]'),1,10) "CUS_DEAL_SLIP",NUMCAST(e
xtractValue(x.xmlrecord,'/row/c40[@m="6"]')) "nCUS_DEAL_SLIP",SUBSTR(extractValu
e(x.xmlrecord,'/row/c40[@m="2"]'),1,20) "CAPITAL",NUMCAST(extractValue(x.xmlreco
rd,'/row/c40[@m="2"]')) "nCAPITAL",SUBSTR(extractValue(x.xmlrecord,'/row/c40[@m=
"5"]'),1,7) "ALT_ID_CUST",NUMCAST(extractValue(x.xmlrecord,'/row/c40[@m="5"]'))
"nALT_ID_CUST",SUBSTR(extractValue(x.xmlrecord,'/row/c42'),1,4) "RECORD_STATUS",
NUMCAST(extractValue(x.xmlrecord,'/row/c42')) "nRECORD_STATUS",SUBSTR(extractVal
ue(x.xmlrecord,'/row/c43'),1,4) "CURR_NO",NUMCAST(extractValue(x.xmlrecord,'/row
/c43')) "nCURR_NO",SUBSTR(extractValue(x.xmlrecord,'/row/c46'),1,20) "AUTHORISER
",NUMCAST(extractValue(x.xmlrecord,'/row/c46')) "nAUTHORISER",SUBSTR(extractValu
e(x.xmlrecord,'/row/c47'),1,11) "CO_CODE",NUMCAST(extractValue(x.xmlrecord,'/row
/c47')) "nCO_CODE",SUBSTR(extractValue(x.xmlrecord,'/row/c48'),1,4) "DEPT_CODE",
NUMCAST(extractValue(x.xmlrecord,'/row/c48')) "nDEPT_CODE",SUBSTR(extractValue(x
.xmlrecord,'/row/c49'),1,16) "AUDITOR_CODE",NUMCAST(extractValue(x.xmlrecord,'/r
ow/c49')) "nAUDITOR_CODE",SUBSTR(extractValue(x.xmlrecord,'/row/c50'),1,15) "AUD
IT_DATE_TIME",NUMCAST(extractValue(x.xmlrecord,'/row/c50')) "nAUDIT_DATE_TIME"
 FROM CUSTOMER x
V_CUSTOMER generated
ClosedMSSQL 
$ create-view -v CUSTOMER
Script name = XMLView_V_CUSTOMER.sql
Script = DROP VIEW V_CUSTOMER
/
CREATE VIEW V_CUSTOMER
 AS SELECT x.RECID,x.XMLRECORD,
XMLRECORD.value('(/row/c1)[1]','NVARCHAR(10)') as MNEMONIC
,dbo.numcast(XMLRECORD.value('(/row/c1)[1]','NVARCHAR(10)')) as nMNEMONIC
,XMLRECORD.value('(/row/c10)[1]','NVARCHAR(4)') as SECTOR
,dbo.numcast(XMLRECORD.value('(/row/c10)[1]','NVARCHAR(4)')) as nSECTOR
,XMLRECORD.value('(/row/c11)[1]','NVARCHAR(4)') as ACCOUNT_OFFICER
,dbo.numcast(XMLRECORD.value('(/row/c11)[1]','NVARCHAR(4)')) as nACCOUNT_OFFICER
,XMLRECORD.value('(/row/c13)[1]','NVARCHAR(4)') as INDUSTRY
,dbo.numcast(XMLRECORD.value('(/row/c13)[1]','NVARCHAR(4)')) as nINDUSTRY
,XMLRECORD.value('(/row/c14)[1]','NVARCHAR(4)') as TARGET
,dbo.numcast(XMLRECORD.value('(/row/c14)[1]','NVARCHAR(4)')) as nTARGET
,XMLRECORD.value('(/row/c15)[1]','NVARCHAR(2)') as NATIONALITY
,dbo.numcast(XMLRECORD.value('(/row/c15)[1]','NVARCHAR(2)')) as nNATIONALITY
,XMLRECORD.value('(/row/c16)[1]','NVARCHAR(4)') as CUSTOMER_STATUS
,dbo.numcast(XMLRECORD.value('(/row/c16)[1]','NVARCHAR(4)')) as nCUSTOMER_STATUS
,XMLRECORD.value('(/row/c17)[1]','NVARCHAR(2)') as RESIDENCE
,dbo.numcast(XMLRECORD.value('(/row/c17)[1]','NVARCHAR(2)')) as nRESIDENCE
,XMLRECORD.value('(/row/c18)[1]','NVARCHAR(11)') as CONTACT_DATE
,dbo.numcast(XMLRECORD.value('(/row/c18)[1]','NVARCHAR(11)')) as nCONTACT_DATE
,XMLRECORD.value('(/row/c19)[1]','NVARCHAR(35)') as INTRODUCER
,dbo.numcast(XMLRECORD.value('(/row/c19)[1]','NVARCHAR(35)')) as nINTRODUCER
,XMLRECORD.value('(/row/c21)[1]','NVARCHAR(20)') as LEGAL_ID
,dbo.numcast(XMLRECORD.value('(/row/c21)[1]','NVARCHAR(20)')) as nLEGAL_ID
,XMLRECORD.value('(/row/c22)[1]','NVARCHAR(17)') as REVIEW_FREQUENCY
,dbo.numcast(XMLRECORD.value('(/row/c22)[1]','NVARCHAR(17)')) as nREVIEW_FREQUENCY
,XMLRECORD.value('(/row/c23)[1]','NVARCHAR(11)') as BIRTH_INCORP_DATE
,dbo.numcast(XMLRECORD.value('(/row/c23)[1]','NVARCHAR(11)')) as nBIRTH_INCORP_DATE
,XMLRECORD.value('(/row/c24)[1]','NVARCHAR(10)') as GLOBAL_CUSTOMER
,dbo.numcast(XMLRECORD.value('(/row/c24)[1]','NVARCHAR(10)')) as nGLOBAL_CUSTOMER
,XMLRECORD.value('(/row/c25)[1]','NVARCHAR(10)') as CUSTOMER_LIABILITY
,dbo.numcast(XMLRECORD.value('(/row/c25)[1]','NVARCHAR(10)')) as nCUSTOMER_LIABILITY
,XMLRECORD.value('(/row/c26)[1]','NVARCHAR(2)') as LANGUAGE
,dbo.numcast(XMLRECORD.value('(/row/c26)[1]','NVARCHAR(2)')) as nLANGUAGE
,XMLRECORD.value('(/row/c27)[1]','NVARCHAR(2)') as POSTING_RESTRICT
,dbo.numcast(XMLRECORD.value('(/row/c27)[1]','NVARCHAR(2)')) as nPOSTING_RESTRICT
,XMLRECORD.value('(/row/c28)[1]','NVARCHAR(4)') as DISPO_OFFICER
,dbo.numcast(XMLRECORD.value('(/row/c28)[1]','NVARCHAR(4)')) as nDISPO_OFFICER
,XMLRECORD.value('(/row/c31)[1]','NVARCHAR(11)') as COMPANY_BOOK
,dbo.numcast(XMLRECORD.value('(/row/c31)[1]','NVARCHAR(11)')) as nCOMPANY_BOOK
,XMLRECORD.value('(/row/c32)[1]','NVARCHAR(3)') as CONFID_TXT
,dbo.numcast(XMLRECORD.value('(/row/c32)[1]','NVARCHAR(3)')) as nCONFID_TXT
,XMLRECORD.value('(/row/c33)[1]','NVARCHAR(3)') as DISPO_EXEMPT
,dbo.numcast(XMLRECORD.value('(/row/c33)[1]','NVARCHAR(3)')) as nDISPO_EXEMPT
,XMLRECORD.value('(/row/c34)[1]','NVARCHAR(3)') as ISSUE_CHEQUES
,dbo.numcast(XMLRECORD.value('(/row/c34)[1]','NVARCHAR(3)')) as nISSUE_CHEQUES
,XMLRECORD.value('(/row/c35)[1]','NVARCHAR(3)') as CLS_CPARTY
,dbo.numcast(XMLRECORD.value('(/row/c35)[1]','NVARCHAR(3)')) as nCLS_CPARTY
,XMLRECORD.value('(/row/c36)[1]','NVARCHAR(3)') as FX_COMM_GROUP_ID
,dbo.numcast(XMLRECORD.value('(/row/c36)[1]','NVARCHAR(3)')) as nFX_COMM_GROUP_ID
,XMLRECORD.value('(/row/c37)[1]','NVARCHAR(9)') as RESIDENCE_REGION
,dbo.numcast(XMLRECORD.value('(/row/c37)[1]','NVARCHAR(9)')) as nRESIDENCE_REGION
,XMLRECORD.value('(/row/c38)[1]','NVARCHAR(3)') as ASSET_CLASS
,dbo.numcast(XMLRECORD.value('(/row/c38)[1]','NVARCHAR(3)')) as nASSET_CLASS
,XMLRECORD.value('(/row/c40[1])[1]','NVARCHAR(1)') as SIGNATURE_Y_N
,dbo.numcast(XMLRECORD.value('(/row/c40[1])[1]','NVARCHAR(1)')) as nSIGNATURE_Y_N
,XMLRECORD.value('(/row/c40[1])[1]','NVARCHAR(4)') as SEC_TYPE_TRADE
,dbo.numcast(XMLRECORD.value('(/row/c40[1])[1]','NVARCHAR(4)')) as nSEC_TYPE_TRADE
,XMLRECORD.value('(/row/c40[@m=3])[1]','NVARCHAR(9)') as OPEN_DATE
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=3])[1]','NVARCHAR(9)')) as nOPEN_DATE
,XMLRECORD.value('(/row/c40[@m=4])[1]','NVARCHAR(12)') as FID_DEPOSIT_REF
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=4])[1]','NVARCHAR(12)')) as nFID_DEPOSIT_REF
,XMLRECORD.value('(/row/c40[@m=6])[1]','NVARCHAR(10)') as CUS_DEAL_SLIP
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=6])[1]','NVARCHAR(10)')) as nCUS_DEAL_SLIP
,XMLRECORD.value('(/row/c40[@m=2])[1]','NVARCHAR(4)') as SEC_TYP_MGT_FEE
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=2])[1]','NVARCHAR(4)')) as nSEC_TYP_MGT_FEE
,XMLRECORD.value('(/row/c40[@m=5])[1]','NVARCHAR(15)') as RELIGION
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=5])[1]','NVARCHAR(15)')) as nRELIGION
,XMLRECORD.value('(/row/c40[@m=5])[1]','NVARCHAR(7)') as ALT_ID_CUST
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=5])[1]','NVARCHAR(7)')) as nALT_ID_CUST
,XMLRECORD.value('(/row/c40[@m=2])[1]','NVARCHAR(20)') as CAPITAL
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=2])[1]','NVARCHAR(20)')) as nCAPITAL
,XMLRECORD.value('(/row/c40[@m=3])[1]','NVARCHAR(4)') as SEC_TYPE_SAFEKP
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=3])[1]','NVARCHAR(4)')) as nSEC_TYPE_SAFEKP
,XMLRECORD.value('(/row/c40[@m=4])[1]','NVARCHAR(3)') as GB_FGN_RESIDENT
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=4])[1]','NVARCHAR(3)')) as nGB_FGN_RESIDENT
,XMLRECORD.value('(/row/c40[@m=6])[1]','NVARCHAR(16)') as PREF_CLIENT
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=6])[1]','NVARCHAR(16)')) as nPREF_CLIENT
,XMLRECORD.value('(/row/c42)[1]','NVARCHAR(4)') as RECORD_STATUS
,dbo.numcast(XMLRECORD.value('(/row/c42)[1]','NVARCHAR(4)')) as nRECORD_STATUS
,XMLRECORD.value('(/row/c43)[1]','NVARCHAR(4)') as CURR_NO
,dbo.numcast(XMLRECORD.value('(/row/c43)[1]','NVARCHAR(4)')) as nCURR_NO
,XMLRECORD.value('(/row/c46)[1]','NVARCHAR(20)') as AUTHORISER
,dbo.numcast(XMLRECORD.value('(/row/c46)[1]','NVARCHAR(20)')) as nAUTHORISER
,XMLRECORD.value('(/row/c47)[1]','NVARCHAR(11)') as CO_CODE
,dbo.numcast(XMLRECORD.value('(/row/c47)[1]','NVARCHAR(11)')) as nCO_CODE
,XMLRECORD.value('(/row/c48)[1]','NVARCHAR(4)') as DEPT_CODE
,dbo.numcast(XMLRECORD.value('(/row/c48)[1]','NVARCHAR(4)')) as nDEPT_CODE
,XMLRECORD.value('(/row/c49)[1]','NVARCHAR(16)') as AUDITOR_CODE
,dbo.numcast(XMLRECORD.value('(/row/c49)[1]','NVARCHAR(16)')) as nAUDITOR_CODE
,XMLRECORD.value('(/row/c50)[1]','NVARCHAR(15)') as AUDIT_DATE_TIME
,dbo.numcast(XMLRECORD.value('(/row/c50)[1]','NVARCHAR(15)')) as nAUDIT_DATE_TIME
 FROM CUSTOMER x
/
 
Now processing
==============
DROP VIEW V_CUSTOMER
 
Now processing
==============
CREATE VIEW V_CUSTOMER
 AS SELECT x.RECID,x.XMLRECORD,
XMLRECORD.value('(/row/c1)[1]','NVARCHAR(10)') as MNEMONIC
,dbo.numcast(XMLRECORD.value('(/row/c1)[1]','NVARCHAR(10)')) as nMNEMONIC
,XMLRECORD.value('(/row/c10)[1]','NVARCHAR(4)') as SECTOR
,dbo.numcast(XMLRECORD.value('(/row/c10)[1]','NVARCHAR(4)')) as nSECTOR
,XMLRECORD.value('(/row/c11)[1]','NVARCHAR(4)') as ACCOUNT_OFFICER
,dbo.numcast(XMLRECORD.value('(/row/c11)[1]','NVARCHAR(4)')) as nACCOUNT_OFFICER
,XMLRECORD.value('(/row/c13)[1]','NVARCHAR(4)') as INDUSTRY
,dbo.numcast(XMLRECORD.value('(/row/c13)[1]','NVARCHAR(4)')) as nINDUSTRY
,XMLRECORD.value('(/row/c14)[1]','NVARCHAR(4)') as TARGET
,dbo.numcast(XMLRECORD.value('(/row/c14)[1]','NVARCHAR(4)')) as nTARGET
,XMLRECORD.value('(/row/c15)[1]','NVARCHAR(2)') as NATIONALITY
,dbo.numcast(XMLRECORD.value('(/row/c15)[1]','NVARCHAR(2)')) as nNATIONALITY
,XMLRECORD.value('(/row/c16)[1]','NVARCHAR(4)') as CUSTOMER_STATUS
,dbo.numcast(XMLRECORD.value('(/row/c16)[1]','NVARCHAR(4)')) as nCUSTOMER_STATUS
,XMLRECORD.value('(/row/c17)[1]','NVARCHAR(2)') as RESIDENCE
,dbo.numcast(XMLRECORD.value('(/row/c17)[1]','NVARCHAR(2)')) as nRESIDENCE
,XMLRECORD.value('(/row/c18)[1]','NVARCHAR(11)') as CONTACT_DATE
,dbo.numcast(XMLRECORD.value('(/row/c18)[1]','NVARCHAR(11)')) as nCONTACT_DATE
,XMLRECORD.value('(/row/c19)[1]','NVARCHAR(35)') as INTRODUCER
,dbo.numcast(XMLRECORD.value('(/row/c19)[1]','NVARCHAR(35)')) as nINTRODUCER
,XMLRECORD.value('(/row/c21)[1]','NVARCHAR(20)') as LEGAL_ID
,dbo.numcast(XMLRECORD.value('(/row/c21)[1]','NVARCHAR(20)')) as nLEGAL_ID
,XMLRECORD.value('(/row/c22)[1]','NVARCHAR(17)') as REVIEW_FREQUENCY
,dbo.numcast(XMLRECORD.value('(/row/c22)[1]','NVARCHAR(17)')) as nREVIEW_FREQUENCY
,XMLRECORD.value('(/row/c23)[1]','NVARCHAR(11)') as BIRTH_INCORP_DATE
,dbo.numcast(XMLRECORD.value('(/row/c23)[1]','NVARCHAR(11)')) as nBIRTH_INCORP_DATE
,XMLRECORD.value('(/row/c24)[1]','NVARCHAR(10)') as GLOBAL_CUSTOMER
,dbo.numcast(XMLRECORD.value('(/row/c24)[1]','NVARCHAR(10)')) as nGLOBAL_CUSTOMER
,XMLRECORD.value('(/row/c25)[1]','NVARCHAR(10)') as CUSTOMER_LIABILITY
,dbo.numcast(XMLRECORD.value('(/row/c25)[1]','NVARCHAR(10)')) as nCUSTOMER_LIABILITY
,XMLRECORD.value('(/row/c26)[1]','NVARCHAR(2)') as LANGUAGE
,dbo.numcast(XMLRECORD.value('(/row/c26)[1]','NVARCHAR(2)')) as nLANGUAGE
,XMLRECORD.value('(/row/c27)[1]','NVARCHAR(2)') as POSTING_RESTRICT
,dbo.numcast(XMLRECORD.value('(/row/c27)[1]','NVARCHAR(2)')) as nPOSTING_RESTRICT
,XMLRECORD.value('(/row/c28)[1]','NVARCHAR(4)') as DISPO_OFFICER
,dbo.numcast(XMLRECORD.value('(/row/c28)[1]','NVARCHAR(4)')) as nDISPO_OFFICER
,XMLRECORD.value('(/row/c31)[1]','NVARCHAR(11)') as COMPANY_BOOK
,dbo.numcast(XMLRECORD.value('(/row/c31)[1]','NVARCHAR(11)')) as nCOMPANY_BOOK
,XMLRECORD.value('(/row/c32)[1]','NVARCHAR(3)') as CONFID_TXT
,dbo.numcast(XMLRECORD.value('(/row/c32)[1]','NVARCHAR(3)')) as nCONFID_TXT
,XMLRECORD.value('(/row/c33)[1]','NVARCHAR(3)') as DISPO_EXEMPT
,dbo.numcast(XMLRECORD.value('(/row/c33)[1]','NVARCHAR(3)')) as nDISPO_EXEMPT
,XMLRECORD.value('(/row/c34)[1]','NVARCHAR(3)') as ISSUE_CHEQUES
,dbo.numcast(XMLRECORD.value('(/row/c34)[1]','NVARCHAR(3)')) as nISSUE_CHEQUES
,XMLRECORD.value('(/row/c35)[1]','NVARCHAR(3)') as CLS_CPARTY
,dbo.numcast(XMLRECORD.value('(/row/c35)[1]','NVARCHAR(3)')) as nCLS_CPARTY
,XMLRECORD.value('(/row/c36)[1]','NVARCHAR(3)') as FX_COMM_GROUP_ID
,dbo.numcast(XMLRECORD.value('(/row/c36)[1]','NVARCHAR(3)')) as nFX_COMM_GROUP_ID
,XMLRECORD.value('(/row/c37)[1]','NVARCHAR(9)') as RESIDENCE_REGION
,dbo.numcast(XMLRECORD.value('(/row/c37)[1]','NVARCHAR(9)')) as nRESIDENCE_REGION
,XMLRECORD.value('(/row/c38)[1]','NVARCHAR(3)') as ASSET_CLASS
,dbo.numcast(XMLRECORD.value('(/row/c38)[1]','NVARCHAR(3)')) as nASSET_CLASS
,XMLRECORD.value('(/row/c40[1])[1]','NVARCHAR(1)') as SIGNATURE_Y_N
,dbo.numcast(XMLRECORD.value('(/row/c40[1])[1]','NVARCHAR(1)')) as nSIGNATURE_Y_N
,XMLRECORD.value('(/row/c40[1])[1]','NVARCHAR(4)') as SEC_TYPE_TRADE
,dbo.numcast(XMLRECORD.value('(/row/c40[1])[1]','NVARCHAR(4)')) as nSEC_TYPE_TRADE
,XMLRECORD.value('(/row/c40[@m=3])[1]','NVARCHAR(9)') as OPEN_DATE
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=3])[1]','NVARCHAR(9)')) as nOPEN_DATE
,XMLRECORD.value('(/row/c40[@m=4])[1]','NVARCHAR(12)') as FID_DEPOSIT_REF
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=4])[1]','NVARCHAR(12)')) as nFID_DEPOSIT_REF
,XMLRECORD.value('(/row/c40[@m=6])[1]','NVARCHAR(10)') as CUS_DEAL_SLIP
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=6])[1]','NVARCHAR(10)')) as nCUS_DEAL_SLIP
,XMLRECORD.value('(/row/c40[@m=2])[1]','NVARCHAR(4)') as SEC_TYP_MGT_FEE
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=2])[1]','NVARCHAR(4)')) as nSEC_TYP_MGT_FEE
,XMLRECORD.value('(/row/c40[@m=5])[1]','NVARCHAR(15)') as RELIGION
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=5])[1]','NVARCHAR(15)')) as nRELIGION
,XMLRECORD.value('(/row/c40[@m=5])[1]','NVARCHAR(7)') as ALT_ID_CUST
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=5])[1]','NVARCHAR(7)')) as nALT_ID_CUST
,XMLRECORD.value('(/row/c40[@m=2])[1]','NVARCHAR(20)') as CAPITAL
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=2])[1]','NVARCHAR(20)')) as nCAPITAL
,XMLRECORD.value('(/row/c40[@m=3])[1]','NVARCHAR(4)') as SEC_TYPE_SAFEKP
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=3])[1]','NVARCHAR(4)')) as nSEC_TYPE_SAFEKP
,XMLRECORD.value('(/row/c40[@m=4])[1]','NVARCHAR(3)') as GB_FGN_RESIDENT
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=4])[1]','NVARCHAR(3)')) as nGB_FGN_RESIDENT
,XMLRECORD.value('(/row/c40[@m=6])[1]','NVARCHAR(16)') as PREF_CLIENT
,dbo.numcast(XMLRECORD.value('(/row/c40[@m=6])[1]','NVARCHAR(16)')) as nPREF_CLIENT
,XMLRECORD.value('(/row/c42)[1]','NVARCHAR(4)') as RECORD_STATUS
,dbo.numcast(XMLRECORD.value('(/row/c42)[1]','NVARCHAR(4)')) as nRECORD_STATUS
,XMLRECORD.value('(/row/c43)[1]','NVARCHAR(4)') as CURR_NO
,dbo.numcast(XMLRECORD.value('(/row/c43)[1]','NVARCHAR(4)')) as nCURR_NO
,XMLRECORD.value('(/row/c46)[1]','NVARCHAR(20)') as AUTHORISER
,dbo.numcast(XMLRECORD.value('(/row/c46)[1]','NVARCHAR(20)')) as nAUTHORISER
,XMLRECORD.value('(/row/c47)[1]','NVARCHAR(11)') as CO_CODE
,dbo.numcast(XMLRECORD.value('(/row/c47)[1]','NVARCHAR(11)')) as nCO_CODE
,XMLRECORD.value('(/row/c48)[1]','NVARCHAR(4)') as DEPT_CODE
,dbo.numcast(XMLRECORD.value('(/row/c48)[1]','NVARCHAR(4)')) as nDEPT_CODE
,XMLRECORD.value('(/row/c49)[1]','NVARCHAR(16)') as AUDITOR_CODE
,dbo.numcast(XMLRECORD.value('(/row/c49)[1]','NVARCHAR(16)')) as nAUDITOR_CODE
,XMLRECORD.value('(/row/c50)[1]','NVARCHAR(15)') as AUDIT_DATE_TIME
,dbo.numcast(XMLRECORD.value('(/row/c50)[1]','NVARCHAR(15)')) as nAUDIT_DATE_TIME
 FROM CUSTOMER x
 
V_CUSTOMER generated

Appendix C- Index Examples

The following are some examples of index creation on the CUSTOMER record. The first is on the Mnemonic field, which is a left-justified field. The second is on the Account Officer field, which is right-justified. Finally, we will show how you can create a left and right justified dictionary (using ACCOUNT.OFFICER again). Left justified indicates alpha-numeric and right-justified indicate numeric.

NOTE: In the examples in this section we have used the -v option to show the resulting script contents of SQL commands.

Firstly the index on MNEMONIC.

Closedcreate-extindex -v CUSTOMER MNEMONIC

This section provides examples for DB2 and Oracle.

ClosedDB2 
$ create-extindex -v CUSTOMER MNEMONIC
Script name = XMLView_MNEMONIC.sql
Script = DROP INDEX ix_CUSTOMER_c1
/
CREATE INDEX ix_CUSTOMER_c1 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c1' as sql VARCHAR(10)
/
 
Now processing
==============
DROP INDEX ix_CUSTOMER_c1
Now processing
==============
CREATE INDEX ix_CUSTOMER_c1 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c1' as sql VARCHAR(10)
Index on MNEMONIC generated
ClosedOracle 
$ create-extindex -v CUSTOMER MNEMONIC
Script name = XMLView_MNEMONIC.sql
Index on MNEMONIC generated
Now processing
==============
DROP INDEX ix_CUSTOMER_c1
Error - ORA-01418: specified index does not exist
 
Now processing
==============
CREATE INDEX ix_CUSTOMER_c1 ON CUSTOMER x (NVL(SUBSTR(extractValue(x.xmlrecord,'/row/c1'),1,10),CHR(1)))
NOTE: The error message after the DROP INDEX ix_CUSTOMER_c1 operation. This is quite normal when creating an index for the first time. When creating an index, if there’s an existing index it must first be removed (using DROP). If this is the first time this index name is being created then the preceding DROP will generate an error.
Closedcreate-extindex -v CUSTOMER ACCOUNT.OFFICER

This section provides examples for DB2 and Oracle.

ClosedDB2 
$ create-extindex -v CUSTOMER ACCOUNT.OFFICER
Script name = XMLView_ACCOUNT_OFFICER.sql
Script = DROP INDEX nix_CUSTOMER_c11
/
CREATE INDEX nix_CUSTOMER_c11 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c11' as sql DOUBLE
/
 
Now processing
==============
DROP INDEX nix_CUSTOMER_c11
 
  SQLSTATE          = 42S12
  Native Error Code = -204
Error message = [IBM][CLI Driver][DB2/NT] SQL0204N  "T24.NIX_CUSTOMER_C11" is an undefined name.  SQLSTATE=42704
 
Now processing
==============
CREATE INDEX nix_CUSTOMER_c11 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c11' as sql DOUBLE
Index on ACCOUNT_OFFICER generated

As shown previously in an Oracle example, there is an error message after the DROP INDEX nix_CUSTOMER_c11 operation. You can also see that the index on ACCOUNT.OFFICER generated a numeric index by default. This is because it is a right-justified dictionary.

ClosedOracle 
$ create-extindex -v CUSTOMER ACCOUNT.OFFICER
Script name = XMLView_ACCOUNT.OFFICER.sql
Index on ACCOUNT.OFFICER generated
Now processing
==============
DROP INDEX nix_CUSTOMER_c11
Now processing
==============
CREATE INDEX nix_CUSTOMER_c11 ON CUSTOMER x (NVL(NUMCAST(extractValue(x.xmlrecord,'/row/c11')),0))
Closedcreate-extindex -v -r -n CUSTOMER ACCOUNT.OFFICER

This section provides examples for DB2 and Oracle.

ClosedDB2 
$ create-extindex -v -r -n CUSTOMER ACCOUNT.OFFICER
Script name = XMLView_ACCOUNT_OFFICER.sql
Script = DROP INDEX ix_CUSTOMER_c11
/
CREATE INDEX ix_CUSTOMER_c11 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c11' as sql VARCHAR(4)
/
DROP INDEX nix_CUSTOMER_c11
/
CREATE INDEX nix_CUSTOMER_c11 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c11' as sql DOUBLE
/
 
Now processing
==============
DROP INDEX ix_CUSTOMER_c11
 
  SQLSTATE          = 42S12
  Native Error Code = -204
Error message = [IBM][CLI Driver][DB2/NT] SQL0204N  "T24.IX_CUSTOMER_C11" is an undefined name.  SQLSTATE=42704
 
 
Now processing
==============
CREATE INDEX ix_CUSTOMER_c11 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c11' as sql VARCHAR(4)
Now processing
==============
DROP INDEX nix_CUSTOMER_c11
Now processing
==============
CREATE INDEX nix_CUSTOMER_c11 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c11' as sql DOUBLE
Index on ACCOUNT_OFFICER generated
ClosedOracle 
$ create-extindex -v -r -n CUSTOMER ACCOUNT.OFFICER
Script name = XMLView_ACCOUNT.OFFICER.sql
Index on ACCOUNT.OFFICER generated
 
Now processing
==============
DROP INDEX ix_CUSTOMER_c11
Now processing
==============
CREATE INDEX ix_CUSTOMER_c11 ON CUSTOMER x (NVL(SUBSTR(extractValue(x.xmlrecord,'/row/c11'),1,4),CHR(1)))
Now processing
==============
DROP INDEX nix_CUSTOMER_c11
Now processing
==============
CREATE INDEX nix_CUSTOMER_c11 ON CUSTOMER x (NVL(NUMCAST(extractValue(x.xmlrecord,'/row/c11')),0))

Appendix D- SQL Server Index Examples

The following examples show some of the options you can apply to indexing in SQL Server.

ClosedCREATE-EXTINDEX -v CUSTOMER VALUE 
Script name = XMLView_CUSTOMER.sql
Script = IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER')
CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD)
/
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_VALUE')
DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER
/
CREATE XML INDEX ix_CUSTOMER_VALUE ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR VALUE
/
 
Now processing
==============
IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER')
CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD)
 
Now processing
==============
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_VALUE')
DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER
 
Now processing
==============
CREATE XML INDEX ix_CUSTOMER_VALUE ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR VALUE
 
Index on CUSTOMER generated
ClosedCREATE-EXTINDEX -v CUSTOMER PATH VALUE 
Script name = XMLView_CUSTOMER.sql
Script = IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER')
CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD)
/
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_PATH')
DROP INDEX ix_CUSTOMER_PATH ON CUSTOMER
/
CREATE XML INDEX ix_CUSTOMER_PATH ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR PATH
/
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_VALUE')
DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER
/
CREATE XML INDEX ix_CUSTOMER_VALUE ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR VALUE
/
 
Now processing
==============
IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER')
CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD)
 
Now processing
==============
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_PATH')
DROP INDEX ix_CUSTOMER_PATH ON CUSTOMER
 
Now processing
==============
CREATE XML INDEX ix_CUSTOMER_PATH ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR PATH
 
Now processing
==============
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_VALUE')
DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER
 
Now processing
==============
CREATE XML INDEX ix_CUSTOMER_VALUE ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR VALUE
 
Index on CUSTOMER generated

create-extindex -v CUSTOMER ALL
Script name = XMLView_CUSTOMER.sql
Script = IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER')
CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD)
/
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_PATH')
DROP INDEX ix_CUSTOMER_PATH ON CUSTOMER
/
CREATE XML INDEX ix_CUSTOMER_PATH ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR PATH
/
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_PROPERTY')
DROP INDEX ix_CUSTOMER_PROPERTY ON CUSTOMER
/
CREATE XML INDEX ix_CUSTOMER_PROPERTY ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR PROPERTY
/
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_VALUE')
DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER
/
CREATE XML INDEX ix_CUSTOMER_VALUE ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR VALUE
/
 
Now processing
==============
IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER')
CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD)
 
Now processing
==============
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_PATH')
DROP INDEX ix_CUSTOMER_PATH ON CUSTOMER
 
Now processing
==============
CREATE XML INDEX ix_CUSTOMER_PATH ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR PATH
 
Now processing
==============
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_PROPERTY')
DROP INDEX ix_CUSTOMER_PROPERTY ON CUSTOMER
 
Now processing
==============
CREATE XML INDEX ix_CUSTOMER_PROPERTY ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR PROPERTY
 
Now processing
==============
IF EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER_VALUE')
DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER
 
Now processing
==============
CREATE XML INDEX ix_CUSTOMER_VALUE ON CUSTOMER(XMLRECORD) USING XML INDEX ix_CUSTOMER FOR VALUE
 
Index on CUSTOMER generated

Bookmark Name Actions
BACK

In this topic

Feedback
x

Are you sure you want to log-off?