Hi Mike,
I use a SET based approach as much as possible, so I recommend using the
XMLTABLE function to simplify your code and speed up performance.
This code example uses two SQL statements. The first extracts the
one-to-one relationship SET data out of the XML document, with the
"CodedValues" one-to-many set extracted as a single XML column. The second
processes the "CodedValues" XML into a second result SET. The advantage of
using a two step process in this case is you wind up with a design that
handles any number of "CodedValue" element rows.
The first statement will need to capture the "CodedValues" into an XML
variable, and the second statement will need to be modified to use that XML
variable instead of the hardcoded XML that I've included for initial
testing and example purposes. These examples run "as is" in the ACS or
Navigator "Run SQL Script" feature.
Using XMLTABLE eliminates a lot of glue code. This article discusses
XMLTABLE in greater detail:
http://www.itjungle.com/fhg/fhg061312-story02.html
The net result is two SQL result sets, that you process like any other SQL
result set.
To use in RPG, these need to be enhanced to include the "INTO" clauses. I
made all columns other than CODED_VALUES defined as VARCHAR(128). Change
that code as needed.
with CTE_XML ( XML_FULL_DOC ) as (
values xmlparse( document
'<GPCodedValueDomain2 xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance
"
xmlns:xs="
http://www.w3.org/2001/XMLSchema";
xmlns:typens="
http://www.esri.com/schemas/ArcGIS/10.1
"
xsi:type="typens:GPCodedValueDomain2">
<DomainName>Pipe Casing Reason</DomainName>
<FieldType>esriFieldTypeString</FieldType>
<MergePolicy>esriMPTDefaultValue</MergePolicy>
<SplitPolicy>esriSPTDuplicate</SplitPolicy>
<Description />
<Owner>DBO</Owner>
<CodedValues xsi:type="typens:ArrayOfCodedValue">
<CodedValue xsi:type="typens:CodedValue">
<Name>Highway</Name>
<Code xsi:type="xs:string">Highway</Code>
</CodedValue>
<CodedValue xsi:type="typens:CodedValue">
<Name>Railroad</Name>
<Code xsi:type="xs:string">Railroad</Code>
</CodedValue>
<CodedValue xsi:type="typens:CodedValue">
<Name>Street</Name>
<Code xsi:type="xs:string">Street</Code>
</CodedValue>
<CodedValue xsi:type="typens:CodedValue">
<Name>Water</Name>
<Code xsi:type="xs:string">Water</Code>
</CodedValue>
</CodedValues>
</GPCodedValueDomain2>' )
) ,
CTE_PARSE as (
select XT.*
from CTE_XML X
left join xmltable(
'$xml_data/GPCodedValueDomain2'
passing X.XML_FULL_DOC as "xml_data"
columns
SEQ for ordinality
,DOMAIN_NAME varchar(128) path 'DomainName'
,FIELD_TYPE varchar(128) path 'FieldType'
,MERGE_POLICY varchar(128) path 'MergePolicy'
,SPLIT_POLICY varchar(128) path 'SplitPolicy'
,DESCRIPTION varchar(128) path 'Description'
,OWNER varchar(128) path 'Owner'
,CODED_VALUES XML path 'CodedValues'
) as XT on 1 = 1
)
select *
from CTE_PARSE
;
with CTE_XML ( XML_CODED_VALUES_DOC ) as (
values xmlparse( document
'<DOC xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance";>' concat
'<CodedValues xsi:type="typens:ArrayOfCodedValue">
<CodedValue xsi:type="typens:CodedValue">
<Name>Highway</Name>
<Code xsi:type="xs:string">Highway</Code>
</CodedValue>
<CodedValue xsi:type="typens:CodedValue">
<Name>Railroad</Name>
<Code xsi:type="xs:string">Railroad</Code>
</CodedValue>
<CodedValue xsi:type="typens:CodedValue">
<Name>Street</Name>
<Code xsi:type="xs:string">Street</Code>
</CodedValue>
<CodedValue xsi:type="typens:CodedValue">
<Name>Water</Name>
<Code xsi:type="xs:string">Water</Code>
</CodedValue>
</CodedValues>' concat
'</DOC>'
)
) ,
CTE_PARSE as (
select XT.*
from CTE_XML X
left join xmltable(
'$xml_data/DOC/CodedValues/CodedValue'
passing X.XML_CODED_VALUES_DOC as "xml_data"
columns
SEQ for ordinality
,CODED_VALUE_NAME varchar(128) path 'Name'
,CODED_VALUE_CODE varchar(128) path 'Code'
) as XT on 1 = 1
)
select *
from CTE_PARSE
;
Mike
date: Wed, 25 May 2016 13:42:38 +0000
from: "Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>
subject: XML processing from a field
I have never had occasion to process xml before, so this is completely new
to me. I don't think that the way I'm being asked to get and parse the
data is the norm.
I found an article http://www.itjungle.com/fhg/fhg080415-story01.html
example (XMLINTOB1) that has code that appears to do something similar to
I what I want to do, but I'm not having any luck.
I am reading a file on SQL SERVER from an ESRI table.
It appears that there is a field that contains XML data.
I have read the file and loaded the data into a character field.
When I run the XML-INTO I get an error code of 302 which says that the xml
document was not an external file. Which of course its not.
Below is a snippet of data . I'm not sure if it is the complete contents
of the field, but hopefully it is enough to indicate what I'm looking at.
<GPCodedValueDomain2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns:xs="http://www.w3.org/2001/XMLSchema"; xmlns:typens="
http://www.esri.com/schemas/ArcGIS/10.1";
xsi:type="typens:GPCodedValueDomain2"><DomainName>Pipe Casing
Reason</DomainName><FieldType>esriFieldTypeString</FieldType><MergePolicy>esriMPTDefaultValue</MergePolicy><SplitPolicy>esriSPTDuplicate</SplitPolicy><Description
/><Owner>DBO</Owner><CodedValues
xsi:type="typens:ArrayOfCodedValue"><CodedValue
xsi:type="typens:CodedValue"><Name>Highway</Name><Code
xsi:type="xs:string">Highway</Code></CodedValue><CodedValue
xsi:type="typens:CodedValue"><Name>Railroad</Name><Code
xsi:type="xs:string">Railroad</Code></CodedValue><CodedValue
xsi:type="typens:CodedValue"><Name>Street</Name><Code
xsi:type="xs:string">Street</Code></CodedValue><CodedValue
xsi:type="typens:CodedValue"><Name>Water</Name><Code
xsi:type="xs:string">Water</Code></CodedValue></CodedValues></GPCodedValueDomain2>
Can I process the xml from a field? If so, what is causing the 302
error?
Any guidance on how to accomplish this is appreciated.
Mike
midrange.com
