Attachment did not work so here go's
XML File
definition:
<?xml version="1.0"
encoding="UTF-8" standalone="yes" ?>
- <collectionxmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"xsi:noNamespaceSchemaLocation="BIBDSL-XML2-SCHEMA-V1.xsd";>
- <record>
<
_subj_code>APF|FH</bic_subj_code>
<auth_title>J![HIYOGISONOF</auth_title>
<barcode>5706152320489</barcode>
<bic_subject>Films
cinema|Thriller / suspense</bic_subject>
<bic
cat_code>SCBX2048</cat_code>
<cert_uk>15</cert_uk>
<colour>Col
& B/W</colour>
<contributor>Ulli Lommel
(Director) | Ulli Lommel (Producer) | Ulli Lommel (Writer) | Ulli Lommel
(Music) | Ulli Lommel (Cinematographer) | Yogi Joshi (Actor) | Elissa Dowling
(Actor) | Jamie Bernadette (Actor) | Cassandra Church (Actor) | Bertrand Pare
(Actor) | Boston Chaet (Actor)</contributor>
-<contributor_list>
-<person>
<person_id>FP17206</person_id>
<person_forename>Ulli</person_forename>
<person_surname>Lommel</person_surname>
<person_display_name>Ulli Lommel</person_display_name>
<role>Director</role>
</person>
-<person>
<person_id>FP17206</person_id>
<person_forename>Ulli</person_forename>
<person_surname>Lommel</person_surname>
<person_display_name>Ulli Lommel</person_display_name>
<role>Producer</role>
</person>
- <person>
<person_id>FP17206</person_id>
<person_forename>Ulli</person_forename>
<person_surname>Lommel</person_surname>
<person_display_name>Ulli
Lommel</person_display_name>
<role>Writer</role>
</person>
- <person>
<person_id>FP17206</person_id>
<person_forename>Ulli</person_forename>
<person_surname>Lommel</person_surname>
<person_display_name>Ulli
Lommel</person_display_name>
<role>Music</role>
</person>
- <person>
<person_id>FP17206</person_id>
<person_forename>Ulli</person_forename>
<person_surname>Lommel</person_surname>
<person_display_name>Ulli
Lommel</person_display_name>
<role>Cinematographer</role>
</person>
- <person>
<person_id>FP170390</person_id>
<person_forename>Yogi</person_forename>
<person_surname>Joshi</person_surname>
<person_display_name>Yogi
Joshi</person_display_name>
<role>Actor</role>
</person>
- <person>
<person_id>FP143189</person_id>
<person_forename>Elissa</person_forename>
<person_surname>Dowling</person_surname>
<person_display_name>Elissa
Dowling</person_display_name>
<role>Actor</role>
</person>
- <person>
<person_id>FP170391</person_id>
<person_forename>Jamie</person_forename>
<person_surname>Bernadette</person_surname>
<person_display_name>Jamie
Bernadette</person_display_name>
<role>Actor</role>
</person>
- <person>
<person_id>FP170392</person_id>
<person_forename>Cassandra</person_forename>
<person_surname>Church</person_surname>
<person_display_name>Cassandra
Church</person_display_name>
<role>Actor</role>
</person>
- <person>
<person_id>FP170393</person_id>
<person_forename>Bertrand</person_forename>
<person_surname>Pare</person_surname>
<person_display_name>Bertrand
Pare</person_display_name>
<role>Actor</role>
</person>
- <person>
<person_id>FP170394</person_id>
<person_forename>Boston</person_forename>
<person_surname>Chaet</person_surname>
<person_display_name>Boston
Chaet</person_display_name>
<role>Actor</role>
</person>
</contributor_list>
<ctitle>Son of
Sam</ctitle>
<description>Low-budget
horror movie, directed by Ulli Lommel, based on the crimes of serial killer,
David Berkowitz. In the 1970s, a man calling himself the 'Son of Sam' is
killing people in New York, carrying out the instructions of murderous voices
inside his head.</description>
<duration>80</duration>
- <extras_1>
- <![CDATA[
Languages(s): English|Interactive Menu|Screen ratio
1:1.33|Dolby Mono
]]>
</extras_1>
<facet>DBFILM|DBFILMMUSIC|DBFILMMUSICGAME|TRADE</facet>
<format>DVD / Normal</format>
<format_code>DVDN</format_code>
<format_type>DVD</format_type>
<genre>Feature|Thriller</genre>
<genre_code>1005</genre_code>
<identifier>SCBX2048|5706152320489|MM00173587</identifier>
<imprint>Scanbox
Entertainment</imprint>
<imprint_code>SCANB</imprint_code>
<imprint_exact>SCANBOXENTERTAINMENT</imprint_exact>
-<jacket_l>
- <![CDATA[
www.bibdsl.co.uk/w10-images/l/mm00173587.jpg
]]>
</jacket_l>
-<jacket_m>
- <![CDATA[
www.bibdsl.co.uk/w10-images/m/mm00173587.jpg
]]>
</jacket_m>
- <jacket_s>
- <![CDATA[
www.bibdsl.co.uk/w10-images/s/mm00173587.jpg
]]>
</jacket_s>
<jacket_ud>07/06/2011</jacket_ud>
<kword_index>Son of
Sam|Yogi Joshi</kword_index>
<name_exact>YOGIJOSHI</name_exact>
<nof_items>1 disc(s)</nof_items>
<primary_name>Yogi
Joshi</primary_name>
<primary_price>B_5 to
_10</primary_price>
<psc>fiction
thriller_suspense</psc>
<psg>feature
thriller</psg>
<publ_country>United States
of America</publ_country>
<ref_no>MM00173587</ref_no>
<region>Region 2</region>
<related_ids>MMID00108677</related_ids>
<sort_alpha>JOSHIYOGISONOFSAM</sort_alpha>
<sort_date>20110627</sort_date>
<sort_month>201106</sort_month>
<sort_name>JOSHIYOGI</sort_name>
<sort_series>ZZZZZZZZSONOFSAM</sort_series>
<sort_title>SONOFSAM</sort_title>
<title_exact>SONOFSAM</title_exact>
<uk_avail>Retail / Rental</uk_avail>
<uk_avail_code>RETR</uk_avail_code>
<uk_dlr_ex_vat>5.44</uk_dlr_ex_vat>
<uk_pubdate>27 Jun 2011</uk_pubdate>
<uk_ex_vat_price>6.66</uk_ex_vat_price>
<uk_vat_price>7.99</uk_vat_price>
<uk_vat_rate>S</uk_vat_rate>
<w10_title_code>MMID00108677</w10_title_code>
<year_of_production>2008</year_of_production>
<last_updated>27082014
- 16:03:11</last_updated>
</record>
</collection>
DB2 Physical file
definition
Number Of Record Internal External Output
Fields And Format Field Name Field Name Buffer
Indicators Length Position
55 30,616 WX@AEB WX@AEB 1
55 30,616 WX#ALT WX#ALT 14
55 30,616 WX#BAS WX#BAS 79
55 30,616 WX#BIC WX#BIC 149
55 30,616 WX#BCC WX#BCC 651
55 30,616 WX#CAT WX#CAT 766
55 30,616 WX#CER WX#CER 781
55 30,616 WX#CEI WX#CEI 784
55 30,616 WX#COL WX#COL 787
55 30,616 WX#CON WX#CON 797
55 30,616 WX#TIT WX#TIT 1,799
55 30,616 WX#DES WX#DES 2,551
55 30,616 WX#DUR WX#DUR 12,553
55 30,616 WX#EDI WX#EDI 12,557
55 30,616 WX#EDN WX#EDN 12,561
55 30,616 WX#EID WX#EID 12,763
55 30,616 WX#EXT WX#EXT 12,797
55 30,616 WX#EX1 WX#EX1 12,823
55 30,616 WX#EX2 WX#EX2 17,825
55 30,616 WX#FMT WX#FMT 22,827
55 30,616 WX#FMC WX#FMC 22,902
55 30,616 WX#FTY WX#FTY 22,907
55 30,616 WX#GNR WX#GNR 22,957
55 30,616 WX#GNC WX#GNC 23,007
55 30,616 WX#IMP WX#IMP 23,017
55 30,616 WX#IMC WX#IMC 23,319
55 30,616 WX#AGE WX#AGE 23,329
55 30,616 WX#JKL WX#JKL 23,339
55 30,616 WX#JKM WX#JKM 23,439
55 30,616 WX#JKS WX#JKS 23,539
55 30,616 WX#ORG WX#ORG 23,639
55 30,616 WX#NUM WX#NUM 23,664
55 30,616 WX#PRA WX#PRA 23,714
55 30,616 WX#PRN WX#PRN 23,724
55 30,616 WX#PRP WX#PRP 23,774
55 30,616 WX#PSC WX#PSC 23,789
55 30,616 WX#PSG WX#PSG 24,041
55 30,616 WX#PRD WX#PRD 24,293
55 30,616 WX#PUB WX#PUB 24,343
55 30,616 WX#REG WX#REG 24,373
55 30,616 WX#SER WX#SER 24,388
55 30,616 WX#SUB WX#SUB 25,140
55 30,616 WX#SUP WX#SUP 25,160
55 30,616 WX#TRK WX#TRK 25,190
55 30,616 WX#TRL WX#TRL 30,192
55 30,616 WX#AVA WX#AVA 30,442
55 30,616 WX#AVC WX#AVC 30,542
55 30,616 WX#DEL WX#DEL 30,546
55 30,616 WX#DLR WX#DLR 30,554
55 30,616 WX#REL WX#REL 30,564
55 30,616 WX#RRX WX#RRX 30,572
55 30,616 WX#RRP WX#RRP 30,582
55 30,616 WX#VAT WX#VAT 30,592
55 30,616 WX#YRP WX#YRP 30,593
55 30,616 WX#UPD WX#UPD 30,597
AS400 command
STRSQL:
1: SET
TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, READ WRITE
2: Insert into f56wxTMP
Select *
From
XMLTABLE('/collection/record'
Passing
xmlparse
(Document
Get_xml_file
('/xml/WEST10/WEST10_DBDVD_C_FULL_20140827_9.xml'))
Columns
"WX@AEB" Varchar(255) Path 'barcode',
"WX#ALT" Varchar(255) Path 'auth_title',
"WX#BAS" Varchar(70) Path 'based_on',
"WX#BIC" Varchar(500) Path 'bic_subject',
"WX#BCC" Varchar(115) Path 'bic_subj_code',
"WX#CAT" Varchar(15) Path 'cat_code',
"WX#CER" Varchar(3) Path 'cert_uk',
"WX#CEI" Varchar(3) Path 'cert_irish',
"WX#COL" Varchar(10) Path 'colour',
"WX#CON" Varchar(1000) Path 'contributor',
"WX#TIT" Varchar(750) Path 'ctitle',
"WX#DES" Varchar(10002) Path 'description',
"WX#DUR" Varchar(4) Path 'duration',
"WK#EDI" Varchar(6) Path 'edi_number',
"WX#EDN" Varchar(202) Path 'edition',
"WX#EID" Varchar(100) Path 'eidr',
"WX#EXT" Varchar(26) Path
'extras_tbc',
"WX#EX1" Varchar(5000) Path
'extras_1',
"WX#EX2" Varchar(5000) Path 'extras_2',
"WX#FMT" Varchar(75) Path 'format',
"WX#FMC" Varchar(5) Path 'format_code',
"WX#FTY" Varchar(50) Path 'format_type',
"WX#GNR" Varchar(50) Path 'genre',
"WX#GNC" Varchar(10) Path 'genre_code',
"WX#IMP" Varchar(300) Path 'imprint',
"WX#IMC" Varchar(10) Path 'imprint_code',
"WX#AGE" Varchar(10) Path 'interest_age',
"WX#JKL" Varchar(100) Path 'jacket_l',
"WX#JKM" Varchar(100) Path 'jacket_m',
"WX#JKS" Varchar(100) Path 'jacket_s',
"WX#ORG" Varchar(25) Path 'language_orig',
"WX#NUM" Varchar(50) Path 'nof_items',
"WX#PRA" Varchar(10) Path 'primary_avail',
"WX#PRN" Varchar(50) Path 'primary_name',
"WX#PRP" Varchar(15) Path 'primary_price',
"WX#PSC" Varchar(250) Path 'psc',
"WX#PSG" Varchar(250) Path 'psg',
"WX#PRD" Varchar(50) Path 'publ_country',
"WX#PUB" Varchar(30) Path 'publisher',
"WX#REG" Varchar(15) Path 'region',
"WX#SER" Varchar(750) Path 'series',
"WX#SUB" Varchar(20) Path 'subtitles',
"WX#SUP" Varchar(30) Path 'supplier',
"WX#TRK" Varchar(5000) Path 'tracks',
"WX#TRL" Varchar(250) Path 'trailer',
"WX#AVA" Varchar(100) Path 'uk_avail',
"WX#AVC" Varchar(4) Path 'uk_avail_code',
"WX#DEL" Varchar(8) Path 'uk_del_date',
"WX#DLR" Varchar(10) Path 'uk_dlr_ex_vat',
"WK#REL" Varchar(8) Path 'uk_pubdate',
"WX#RRX" Varchar(10) Path 'uk_ex_vat_price',
"WX#RRP" Varchar(10) Path 'uk_vat_price',
"WX#VAT" Varchar(1) Path 'uk_vat_rate',
"WX#YRP" Varchar(4) Path 'year_of_production',
"WX#UPD" Varchar(20) Path 'last_updated') x
3: COMMIT
________________________________
From: MARK GOLDEN <mark_golden@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Saturday, 11 October 2014, 17:09
Subject: Re: SQL XML. XMLTABLE 7.1
Hi,
Please accept this is one of my first attempts at this coding.
Regards,
Mark.
________________________________
From: Jon Paris <jon.paris@xxxxxxxxxxxxxx>
To: Midrange-L Midrange-l <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, 9 October 2014, 23:16
Subject: Re: SQL XML. XMLTABLE 7.1
Thanks Mark.
Jon Paris
www.partner400.com
www.SystemiDeveloper.com
On Oct 9, 2014, at 5:09 PM, MARK GOLDEN <mark_golden@xxxxxxxxxxxxxxx> wrote:
Hi Jon,
I will arrange to have the code posted tomorrow. Unfortunately I do not have access to the IBM i server tonight.
Regards,
Mark
________________________________
From: Jon Paris <jon.paris@xxxxxxxxxxxxxx>
To: Midrange-L Midrange-l <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, 9 October 2014, 21:11
Subject: Re: SQL XML. XMLTABLE 7.1
For those of us still struggling with the arcane SQL syntax used to perform this feat Mark it would be really nice if you could post the code to the thread or on code.midrange.com and the link posted here.
Jon Paris
www.partner400.com
www.SystemiDeveloper.com
On Oct 9, 2014, at 1:21 PM, MARK GOLDEN <mark_golden@xxxxxxxxxxxxxxx> wrote:
Hi,
Due to your help and pointers I now have an XML file being converted to DB2 with the use to XMLTABLE, XML_GET_FILE, etc.
I do have one more question. Within the data is CDATA strings. I know their purpose but the parser is returning invalid data. Between the ![CDATA[' and ']]' is good but the ![[] is being returned with invalid data. Is this the old problem of [] being translated incorrectly due to CCSIDs or is there
something I can do within the XML process to strip these characters?
Thanks,
Mark.
________________________________
From: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Monday, 6 October 2014, 19:56
Subject: Re: AW: SQL XML. XMLTABLE 7.1
Excellent!
On 10/6/2014 1:41 PM, Gary Thompson wrote:
Vern,
Thank you.
Both links look spot on for me!
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Monday, October 06, 2014 10:27 AM
To: Midrange Systems Technical Discussion
Subject: Re: AW: SQL XML. XMLTABLE 7.1
Gary
For more on the support in SQL, go to this link -
http://ibmsystemsmag.blogs.com/i_can/xml/
Page down to find the 1st of 2 articles by Nick Lawrence - a number of links to helpful and sometimes almost overwhelming stuff from IBM.
To get going on XML itself, I suggest http://www.w3schools.com and look for the XML tutorial - excellent place for lots of web-related stuff.
HTH
Vern
On 10/6/2014 8:42 AM, Gary Thompson wrote:
Interesting - about time Team Mudville learns about XML so any hints on where to start are appreciated.
We're at V7R1
I don't see the XML tool-kit on our V7R1 box so I'll ask ops mgr to
install 5733-XT2
We have the XML Files, Ins and Outs and Integrating XML with DB2 redbooks and
XML Toolkit and XML Extender manuals.
Also have XML Toolkit article from ibmsystemsmag . . .
Thanks
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Birgitta Hauser
Sent: Sunday, October 05, 2014 10:26 PM
To: 'Midrange Systems Technical Discussion'
Subject: AW: AW: SQL XML. XMLTABLE 7.1
It is hard to say what happens, without knowing the XML document.
In either way if it is stored in the IFS in the path
/erl/customerinfo, can you access the XML document as follows (from
IBM I Navigator's run an SQL script / STRSQL does not support it!)
Values(XMLParse(Get_XML_File('/erl/customerinfo/YourxmlDoc.xml'));
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag
von MARK GOLDEN
Gesendet: Sunday, 05.10 2014 22:10
An: Midrange Systems Technical Discussion
Betreff: Re: AW: SQL XML. XMLTABLE 7.1
Hi Birgitta,
This is still new to me as you can see!!
My XML document is stored in the IFS with path /erl/customerinfo.
Mit freundlichen Grüßen / Best regards
Mark Golden.
________________________________
From: Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Sent: Sunday, 5 October 2014, 19:26
Subject: AW: SQL XML. XMLTABLE 7.1
Where is your XML Document located or what's the name of your XML document?
If your XML document is located within the IFS you need to refer to
the IFS file, i.e. retrieve a XML locator with the GET_XML_FILE function:
Something like this:
Select a.*
from XMLTABLE('/customerinfo'
passing xmlparse(Document
Get_xml_file('/YourDir/YourSubDir/YourXMLDoc.xml'))
Columns CustId Integer Path '@CId',
CustName VarChar(30) Path 'name',
Str VarChar(40) Path 'assr/street',
Place VarChar(30) Path 'addr/city') a;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag
von MARK GOLDEN
Gesendet: Sunday, 05.10 2014 15:44
An: MIDRANGE-L@xxxxxxxxxxxx
Betreff: SQL XML. XMLTABLE 7.1
Any help would be greatly appreciated.
I am trying to use SQL, XML, XMLTABLE functionality on IBM i 7.1.
Using the below example from the web which is similar to other
examples I am getting the following error.
INSERT INTO erl/address(cid, name, street, city) SELECT x.custid,
x.custname, x.str, x.place FROM XMLTABLE('$i/customerinfo' PASSING
CAST(? AS
XML) AS "i"
COLUMNS custid INTEGER PATH '@Cid', custname VARCHAR(30) PATH 'name',
str
VARCHAR(40) PATH 'addr/street', place VARCHAR(30) PATH 'addr/city' )
AS x ;
Use of parameter marker not valid. SQL0418. This seems to refer to the
'?' in the CAST section of the statement.
For more info. I am trying to load a XML file into a Physical file
without XML fields/columns defined. So straight XML to PF file field mapping.
Yours Sincerely,
Mark Golden
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
midrange.com
