XML Parsing
Moderators: chulett, rschirm, roy
XML Parsing
Hi,
i need to extract the file contains data in XML format and output should fletten.please see below the input format:
File name:File1.txt
<?xml version="1.0" encoding="UTF-8"?>
<message:Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:message="http://message.xyz.xyz.xyz.com" xsi:type="this is message2">
<key>
<messageId>11111AAAAA</messageId>
</key>
<partitionKeyNumber>22</partitionKeyNumber>
<source>
<IdCD>AAA</IdCD>
<Code>111</Code>
<LocationCode>BBB</LocationCode>
</source>
</message:Message>
<?xml version="1.0" encoding="UTF-8"?>
<message:Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:message="http://message.xyz.xyz.xyz.com" xsi:type="this is message2">
<key>
<messageId>22222BBBBB</messageId>
</key>
<partitionKeyNumber>22</partitionKeyNumber>
<source>
<IdCD>ABC</IdCD>
<Code>222</Code>
<LocationCode>DEF</LocationCode>
</source>
</message:Message>
expected output :
message Messageid partitionKeyNumber IdCD Code LocationCode
---------------------------------------------------------------------------------
this is message1 11111AAAAA 22 AAA 111 BBB
this is message2 22222BBBBB 22 ABC 222 DEF
please suggest in this.
i appreciate for your response.
Kris
i need to extract the file contains data in XML format and output should fletten.please see below the input format:
File name:File1.txt
<?xml version="1.0" encoding="UTF-8"?>
<message:Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:message="http://message.xyz.xyz.xyz.com" xsi:type="this is message2">
<key>
<messageId>11111AAAAA</messageId>
</key>
<partitionKeyNumber>22</partitionKeyNumber>
<source>
<IdCD>AAA</IdCD>
<Code>111</Code>
<LocationCode>BBB</LocationCode>
</source>
</message:Message>
<?xml version="1.0" encoding="UTF-8"?>
<message:Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:message="http://message.xyz.xyz.xyz.com" xsi:type="this is message2">
<key>
<messageId>22222BBBBB</messageId>
</key>
<partitionKeyNumber>22</partitionKeyNumber>
<source>
<IdCD>ABC</IdCD>
<Code>222</Code>
<LocationCode>DEF</LocationCode>
</source>
</message:Message>
expected output :
message Messageid partitionKeyNumber IdCD Code LocationCode
---------------------------------------------------------------------------------
this is message1 11111AAAAA 22 AAA 111 BBB
this is message2 22222BBBBB 22 ABC 222 DEF
please suggest in this.
i appreciate for your response.
Kris
when i tried to open in xml file it is issuing error
"Only one top level element is allowed in an XML document. Error processing resource"
Actually it is .txt file,when i tried by converting .txt to .xml in windows and import metadata it is not allowing.
my quick question is how can we extract xml data if it contains more than one elements like
<?xml version="1.0" encoding="UTF-8"?>
<message:Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:message="http://message.xyz.xyz.xyz.com" xsi:type="this is message2">
.
.
.
.
.
<?xml version="1.0" encoding="UTF-8"?>
<message:Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:message="http://message.xyz.xyz.xyz.com" xsi:type="this is message2">
.
.
.
.
.
Regards
kris
"Only one top level element is allowed in an XML document. Error processing resource"
Actually it is .txt file,when i tried by converting .txt to .xml in windows and import metadata it is not allowing.
my quick question is how can we extract xml data if it contains more than one elements like
<?xml version="1.0" encoding="UTF-8"?>
<message:Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:message="http://message.xyz.xyz.xyz.com" xsi:type="this is message2">
.
.
.
.
.
<?xml version="1.0" encoding="UTF-8"?>
<message:Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:message="http://message.xyz.xyz.xyz.com" xsi:type="this is message2">
.
.
.
.
.
Regards
kris
Datastage User
I did not see any XSD option in import tab.
I renamed File1.txt to File1.xsd and tried to import using XML Table definitions...
it is throwing following error:
Line 2,Position2:Expected Schema root.Make sure that the root element is <schema> and the namespace is 'htt//www.w3.org/2001/XMLSchema' for an XSD schema or 'urn:schemas-microsoft-com-data'for an XDR schema.An error occured at file:///C:/document
I renamed File1.txt to File1.xsd and tried to import using XML Table definitions...
it is throwing following error:
Line 2,Position2:Expected Schema root.Make sure that the root element is <schema> and the namespace is 'htt//www.w3.org/2001/XMLSchema' for an XSD schema or 'urn:schemas-microsoft-com-data'for an XDR schema.An error occured at file:///C:/document
Datastage User
1) Sure there is. When you go to "Open" the file in the XML Meta Data Importer tool, there is a prompt for "Files of type". Both "XML schemas and documents" and "XSD files" will work, as well as the ever popular "All files". Unless it has totally changed for 8.x, that is, but there still must be a way to import from them.
2) You cannot simply rename the file like that. An xsd file is completely different from an XML file. Request the matching xsd from whomever is providing you with the xml.
2) You cannot simply rename the file like that. An xsd file is completely different from an XML file. Request the matching xsd from whomever is providing you with the xml.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
You need to break it up.... you don't have one xml document in your text file ---- you have MANY xml documents in your text file. Each one can be read independently. You will have to break them up........ or get the person/app sending them to you to break them up as individual .txt files, individual messages, individual rows, etc.
Still, using DS to break it up is possible...think of this as just a set of longvarchars that each are delimited by <? xml .....once you have each set of <? xml..... in its own column you'll be able to do what you need.
To import the metadata, just cut/paste one of them into its own xml document and import, but be careful --- the concerns above for having an xsd are valid --- you'll need one if the instances that you have are not complete with all elements and attributes.
Ernie
Still, using DS to break it up is possible...think of this as just a set of longvarchars that each are delimited by <? xml .....once you have each set of <? xml..... in its own column you'll be able to do what you need.
To import the metadata, just cut/paste one of them into its own xml document and import, but be careful --- the concerns above for having an xsd are valid --- you'll need one if the instances that you have are not complete with all elements and attributes.
Ernie
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
poor choice of words above....get each xml section into its own "row". Lots of techniques for doing that.
Ernie
Ernie
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
-
- Premium Member
- Posts: 175
- Joined: Tue Oct 14, 2008 1:48 pm
[quote="eostic"]You need to break it up.... you don't have one xml document in your text file ---- you have MANY xml documents in your text file. Each one can be read independently. You will have to break them up
If we try to split the data with delimiter "<xml", if we dont know how many messages we have then how many columns we have define in the delimited file?
If we try to split the data with delimiter "<xml", if we dont know how many messages we have then how many columns we have define in the delimited file?
Rajesh Kumar