How to split one Line into multiple lines while search

[sdaruna]

In reference to my other post
https://answers.splunk.com/answers/337397/how-to-break-xml-in-search-time.html

I am adding other way of the question.

I have total xml data in a field like below.

<?xml version="1.0" encoding="UTF-8"?>
<Document xsi:noNamespaceSchemaLocation="EPA_GEODATA_v1.0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   
    <FacilitySite registryId="110007915364">
        <FacilitySiteName>GREAT SOUTHERN WOOD PRESERVING INC</FacilitySiteName>
        <LocationAddressText>1100 HIGHWAY 431 NORTH</LocationAddressText>
        <LocalityName>ABBEVILLE</LocalityName>
        <LocationAddressStateCode>AL</LocationAddressStateCode>
    </FacilitySite>
    <FacilitySite registryId="110000369084">
        <FacilitySiteName>REMBRANDT FOODS- ABBEVILLE</FacilitySiteName>
        <LocationAddressText>496 INDUSTRIAL PARK RD</LocationAddressText>
        <LocalityName>ABBEVILLE</LocalityName>
        <LocationAddressStateCode>AL</LocationAddressStateCode>
        <LocationZIPCode>36310</LocationZIPCode>
    </FacilitySite>
    <FacilitySite registryId="110055437718">
        <FacilitySiteName>RITE AID #7092</FacilitySiteName>
        <LocationAddressText>514 KIRKLAND STREET</LocationAddressText>
        <LocalityName>ABBEVILLE</LocalityName>
        <LocationAddressStateCode>AL</LocationAddressStateCode>
        <LocationZIPCode>36310-2700</LocationZIPCode>
        <LatitudeMeasure>31.56149</LatitudeMeasure>
    </FacilitySite>
</Document>

I need to break the entire field into multiple rows. like below.

----------------------------------------------------------------------------------------
          <FacilitySite registryId="110007915364">
                <FacilitySiteName>GREAT SOUTHERN WOOD PRESERVING INC</FacilitySiteName>
                <LocationAddressText>1100 HIGHWAY 431 NORTH</LocationAddressText>
                <LocalityName>ABBEVILLE</LocalityName>
                <LocationAddressStateCode>AL</LocationAddressStateCode>
            </FacilitySite>
----------------------------------------------------------------------------------------
    <FacilitySite registryId="110000369084">
            <FacilitySiteName>REMBRANDT FOODS- ABBEVILLE</FacilitySiteName>
            <LocationAddressText>496 INDUSTRIAL PARK RD</LocationAddressText>
            <LocalityName>ABBEVILLE</LocalityName>
            <LocationAddressStateCode>AL</LocationAddressStateCode>
            <LocationZIPCode>36310</LocationZIPCode>
        </FacilitySite>
----------------------------------------------------------------------------------------
<FacilitySite registryId="110055437718">
            <FacilitySiteName>RITE AID #7092</FacilitySiteName>
            <LocationAddressText>514 KIRKLAND STREET</LocationAddressText>
            <LocalityName>ABBEVILLE</LocalityName>
            <LocationAddressStateCode>AL</LocationAddressStateCode>
            <LocationZIPCode>36310-2700</LocationZIPCode>
            <LatitudeMeasure>31.56149</LatitudeMeasure>
        </FacilitySite>

Please let me know how could i do it. I tried rex, but i do not think that can give multiple rows out of one.

[sundareshr · Accepted solution]

See if this get you going

| xmlkv | spath output=s path=Document.FacilitySite | table s | eval y=mvindex(s, 2) | mvexpand s | table s, y

In the above query, s has all instances of the node, mvexpand breaks them out into separate rows. mvindex(s, 2) gets the 3rd instance.

For more info...

http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/spath

http://docs.splunk.com/Documentation/Splunk/6.2.0/Search/Parsemultivaluefields

[sdaruna]

Hi,

but, the attribute registry id is not coming. Is it coming for your search.? How could you get that.?

[sundareshr]

| spath output=r path=Document.FacilitySite{@registryId} will give you registryId.`

[sdaruna]

NO, sundaresh, I meant, i want both node and the registry id. How can i get two outputs from spath at the same time. >

[sundareshr]

| spath output=s path=Document.FacilitySite | spath output=r path=Document.FacilitySite{@registryId}

Maybe if you explain what you are trying to accomplish, end goal, I can try and give you a better answer.

[sdaruna]

For each node, i need nodedata and registry id. I tried using two xpaths, but they are coming out as two different groups.

[sundareshr]

Now I get it. Try this... Basically, I pull all the nodes into variable, then combine the variables so they a single row for all nodedata. Then split them using the separator and finally, get the appropriate values from the array. Let me know if the below query doesn't work for you.

| spath | rename Document.FacilitySite{@registryId} as r | rename Document.FacilitySite.FacilitySiteName as s | rename Document.FacilitySite.LocationAddressText as a | rename Document.FacilitySite.LocalityName as l | rename Document.FacilitySite.LocationAddressStateCode as c |  eval z=mvzip(r, s, "@@") | eval z=mvzip(z, a, "@@") | eval z=mvzip(z, l, "@@") | eval z=mvzip(z, c, "@@") | mvexpand z | eval site=split(z, "@@") | eval regid=mvindex(site, 0) | eval sitename=mvindex(site, 1) | eval add=mvindex(site, 2) | eval local=mvindex(site, 3) | eval state=mvindex(site, 4) | eval zip=mvindex(site, 5) | table regid, sitename, add, local, state

[sdaruna]

Hi Sundaresh,

I would really thank you for your patience and help. The below is the format like which i am expecting output with registry id and node.

I could not paste it properly in the comment, so i have added it in question it self. Please see the latest edit in question.