I have an xml file in a format where columns are being sent as rows and I need to take a list of values and turn them back into columns. Here is the sample xml:
I know how to return each of the individual value nodes as a row, but I need each value attribute as a column in the result. Not doing so will increase the likelihood of exceeding the maximum size of a multiple occurrence data structure. The data structure should look something like this:
Here is the SQL i wrote that returns 1 row for each attribute value:
Do I need to include the "pivot trick" on this data using common table expressions?
Code:
<RootNode> <Vehicle VIN="3C3CFFBR5ET236538"> <Values> <Value Attribute="Make">Fiat</Value> <Value Attribute="Model">500 Sport</Value> <Value Attribute="Year">2014</Value> <Value Attribute="Engine Type">4 cylinders</Value> <Value Attribute="Body">Passenger Car</Value> <Value Attribute="Fuel">Gas</Value> </Values> </Vehicle> <Vehicle VIN="1NKWL29X1FS326723"> <Values> <Value Attribute="Make">Kenworth</Value> <Value Attribute="Model">Kenworth</Value> <Value Attribute="Year">1985</Value> <Value Attribute="Engine Type">14.0L 6X4</Value> <Value Attribute="Body">Conventional Cab</Value> <Value Attribute="Fuel">Diesel</Value> </Values> </Vehicle> </RootNode>
Code:
dcl-ds Data qualified dim(9999) inz; VIN varchar(17); Make varchar(128); Model varchar(128); EngineType varchar(128); Body varchar(128); Fuel varchar(64); end-ds ;
Here is the SQL i wrote that returns 1 row for each attribute value:
Code:
select x.* from myxml a, xmltable( '$doc/RootNode/Vehicle/Values/Value' passing a.xmlstring as "doc" columns VIN varchar(17) path '../../@VIN' , attribute varchar(128) path '@Attribute' , attributeValue varchar(128) path '.' ) as x ;
Do I need to include the "pivot trick" on this data using common table expressions?
Comment