DSXchange

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

What have you tried, anything? Do you have an xsd for this and have you imported the metadata to create the XPath Expressions?

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

krishna81 wrote:"Only one top level element is allowed in an XML document. Error processing resource"

Your answer is right there... nope.

And the extension doesn't matter, txt v. xml, what matters is the content.

the content is in xml format .

Right, we can see that, which means there should be no reason to "convert it to XML in Windows".

when i am trying to import the metadata the it is throwing following error.

"Line 13,Position 3:The XML declaration is Unexpected. Line 13,Position 3."

What are you importing from? An xsd or the xml file itself? Best to get an xsd if at all possible.

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

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.

Actually file is coming with .txt but contents are in XML format.

Yes, we know. And?

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

poor choice of words above....get each xml section into its own "row". Lots of techniques for doing that.

Ernie

[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?