Solved

adding multiple child elements xml to existing xml or replace value if tag exists from excel


Badge +9

I have an excel spreadsheet with 3 columns (featureclass, tag, summary). I want to update the metadata.xml file that corresponding to the featureclass. These xml files are embedded in a column in an sde table gdb_items. I'm able to extract the xml, but I don't know how to add xml elements. I have seen the xmlUpdater which works but only when that tag exists in the xml. Also, the "tag" field in the excel is a list of tags separated by commas. I need to create multiple <keyword> tags, one for each item. Here's my workspace so far.

shows the xml without the existing "summary" and "tag" <idPrup> <searchKeys> with child nodes <keyword>, respectively

shows xml with those nodes (lines 80 to 86)

icon

Best answer by takashi 4 August 2017, 02:57

View original

16 replies

Userlevel 2
Badge +17

Hi @tnarladni, I think you can use the XMLUpdater in conjunction with an AttributeSplitter. This is a simplified example.

Source XML Document

<?xml version="1.0"?>
<metadata>
    <Esri />
    <dataIdInfo>
        <envirDesc />
        <dataExt />
        <dataExt />
        <dataChar />
    </dataIdInfo>
    <mdLang />
    <distInfo />
</metadata>
Values to Add
Summary: A simplified example describing how to update a metadata XML document
Tags: Centerline, Route, Series, PODS
Workflow

0684Q00000ArKytQAF.png

Result
<?xml version="1.0" encoding="UTF-8"?>
<metadata>
<Esri/>
<dataIdInfo>
<envirDesc/>
<dataExt/>
<dataExt/>
<idPrup>A simplified example describing how to update a metadata XML document</idPrup>
<searchKeys>
<keyword>Centerline</keyword>
<keyword>Route</keyword>
<keyword>Series</keyword>
<keyword>PODS</keyword>
</searchKeys>
<dataChar/>
</dataIdInfo>
<mdLang/>
<distInfo/>
</metadata>
Userlevel 2
Badge +17

Hi @tnarladni, I think you can use the XMLUpdater in conjunction with an AttributeSplitter. This is a simplified example.

Source XML Document

<?xml version="1.0"?>
<metadata>
    <Esri />
    <dataIdInfo>
        <envirDesc />
        <dataExt />
        <dataExt />
        <dataChar />
    </dataIdInfo>
    <mdLang />
    <distInfo />
</metadata>
Values to Add
Summary: A simplified example describing how to update a metadata XML document
Tags: Centerline, Route, Series, PODS
Workflow

0684Q00000ArKytQAF.png

Result
<?xml version="1.0" encoding="UTF-8"?>
<metadata>
<Esri/>
<dataIdInfo>
<envirDesc/>
<dataExt/>
<dataExt/>
<idPrup>A simplified example describing how to update a metadata XML document</idPrup>
<searchKeys>
<keyword>Centerline</keyword>
<keyword>Route</keyword>
<keyword>Series</keyword>
<keyword>PODS</keyword>
</searchKeys>
<dataChar/>
</dataIdInfo>
<mdLang/>
<distInfo/>
</metadata>
In addition, if you would use this XQuery expression to create the <searchKey>  element, the AttributeSplitter could be removed from the workflow.

 

<searchKeys>{
for $tag in fn:tokenize(fme:get-attribute("Tags"), '\s*,\s*')
return <keyword>{$tag}</keyword>
}</searchKeys>

 

Userlevel 2
Badge +17

Hi @tnarladni, I think you can use the XMLUpdater in conjunction with an AttributeSplitter. This is a simplified example.

Source XML Document

<?xml version="1.0"?>
<metadata>
    <Esri />
    <dataIdInfo>
        <envirDesc />
        <dataExt />
        <dataExt />
        <dataChar />
    </dataIdInfo>
    <mdLang />
    <distInfo />
</metadata>
Values to Add
Summary: A simplified example describing how to update a metadata XML document
Tags: Centerline, Route, Series, PODS
Workflow

0684Q00000ArKytQAF.png

Result
<?xml version="1.0" encoding="UTF-8"?>
<metadata>
<Esri/>
<dataIdInfo>
<envirDesc/>
<dataExt/>
<dataExt/>
<idPrup>A simplified example describing how to update a metadata XML document</idPrup>
<searchKeys>
<keyword>Centerline</keyword>
<keyword>Route</keyword>
<keyword>Series</keyword>
<keyword>PODS</keyword>
</searchKeys>
<dataChar/>
</dataIdInfo>
<mdLang/>
<distInfo/>
</metadata>

See also this workspace example: xmlupdater-insert-nodes-example.fmw (FME 2017.0.1.1)

Badge +3

Hi @tnarladni,

XMLUpdater is a good approach to use when updating existing XML documents. However, if you find the XQuery scripting a bit tricky to customize for your needs, the other option is a hybrid approach. Instead of the searchKeys{} loop, you could use an XMLTemplater to create an XML fragment of the searchKeys element with the keyword sub-elements. You will likely need to create a sub-template and feed it with one record for each sub element. Then use XMLUpdater to insert the searchKeys xml fragment. For more working with XMLTemplater see: https://knowledge.safe.com/articles/30940/xml-writing-with-xmltemplater.html

Badge +9

xml-update 

Thank you gentlemen @takashi and @DeanAtSafe for your help. I used both of the answers to get really close to the end result. I am still having trouble with checking whether the node exists before doing something to it. The xmlUpdater assumes the <idPurp> and <searhKeys> nodes do not exist. I have the xmlxQueryUpdater in here, but not sure if that's even the way to go. Here's my latest workspace. could you take a look and help me? Thanks!

Badge +9

Thank you gentlemen @takashi and @DeanAtSafe for your help. I used both of the answers to get really close to the end result. I am still having trouble with checking whether the node exists before doing something to it. The xmlUpdater assumes the <idPurp> and <searhKeys> nodes do not exist. I have the xmlxQueryUpdater in here, but not sure if that's even the way to go. Here's my latest workspace. could you take a look and help me? Thanks!

Badge +9

xml-update 

Thank you gentlemen @takashi and @DeanAtSafe for your help. I used both of the answers to get really close to the end result. I am still having trouble with checking whether the node exists before doing something to it. The xmlUpdater assumes the <idPurp> and <searhKeys> nodes do not exist. I have the xmlxQueryUpdater in here, but not sure if that's even the way to go. Here's my latest workspace. could you take a look and help me? Thanks!

@takashi and @DeanAtSafe, I updated my workspace and used tester to look for the <idPurp> text which is only used for the purpose I'm using it for (updating the summary of the dataset in its metadata). So I guess it would make sense to use this transformer? Let me know if you think I should use something else?

 

Also, I used XMLFormatter to make the xml pretty and it's adding the header <?xml version="1.0"...> to my xml. In the other XML transformers, there's an option to add the header or not, but not the XMLFormatter. Is there a way to remove that?

 

 

 

 

Userlevel 2
Badge +17

xml-update 

Thank you gentlemen @takashi and @DeanAtSafe for your help. I used both of the answers to get really close to the end result. I am still having trouble with checking whether the node exists before doing something to it. The xmlUpdater assumes the <idPurp> and <searhKeys> nodes do not exist. I have the xmlxQueryUpdater in here, but not sure if that's even the way to go. Here's my latest workspace. could you take a look and help me? Thanks! 

I'm not sure what the major issue. If you want to determine if the XML document "geodb_metadata_string" contains "//dataIdInfo/idPurp" element, a possible way is to use an XMLXQueryExtractor with this setting.

 

 

The "_result" attribute of the feature output from the XMLXQueryExtractor will have "true" if the element exists, otherwise "false".

 

 

XQuery Expression:

 

fn:exists(//dataIdInfo/idPurp)

 

0684Q00000ArMNEQA3.png

 

 

 

 

Badge +9
I'm not sure what the major issue. If you want to determine if the XML document "geodb_metadata_string" contains "//dataIdInfo/idPurp" element, a possible way is to use an XMLXQueryExtractor with this setting.

 

 

The "_result" attribute of the feature output from the XMLXQueryExtractor will have "true" if the element exists, otherwise "false".

 

 

XQuery Expression:

 

fn:exists(//dataIdInfo/idPurp)

 

0684Q00000ArMNEQA3.png

 

 

 

 

Thanks @takashi. I would like to use the xml transformers more so I will use that instead of the tester. Do you have a suggestion for the other issue I'm having? that XMLFormatter is adding the header node?

 

Userlevel 2
Badge +17

xml-update 

Thank you gentlemen @takashi and @DeanAtSafe for your help. I used both of the answers to get really close to the end result. I am still having trouble with checking whether the node exists before doing something to it. The xmlUpdater assumes the <idPurp> and <searhKeys> nodes do not exist. I have the xmlxQueryUpdater in here, but not sure if that's even the way to go. Here's my latest workspace. could you take a look and help me? Thanks!

What is other issue do you have?
Badge +3

xml-update 

Thank you gentlemen @takashi and @DeanAtSafe for your help. I used both of the answers to get really close to the end result. I am still having trouble with checking whether the node exists before doing something to it. The xmlUpdater assumes the <idPurp> and <searhKeys> nodes do not exist. I have the xmlxQueryUpdater in here, but not sure if that's even the way to go. Here's my latest workspace. could you take a look and help me? Thanks!

@tnarladni you can remove the XML header by using a StringReplacer to replace '<?xml version="1.0" encoding="UTF-8"?> with "". I understand your suggestion that it would make sense to have this as an option on the XMLFormatter to make this easier. I'll add an enhancement request to that effect.
Badge +2

@tnarladni are you able to reupload your 2nd update-metadata.fmw

 

I've got a similar query and wanted to look at what you'd done, but the link no longer appears to work

 

Thanks

Badge +9

@tnarladni are you able to reupload your 2nd update-metadata.fmw

 

I've got a similar query and wanted to look at what you'd done, but the link no longer appears to work

 

Thanks

Hey @stephstedman

Unfortunately, I'm not working for that company anymore, so I don't have access to that script. I'll reach out to an ex-colleague to see if she's able to upload it.

Badge +2

Hey @stephstedman

Unfortunately, I'm not working for that company anymore, so I don't have access to that script. I'll reach out to an ex-colleague to see if she's able to upload it.

Thanks very much

See also this workspace example: xmlupdater-insert-nodes-example.fmw (FME 2017.0.1.1)

Hi, I am also trying to insert sibling nodes to an xml attribute. Can you share the above mentioned fmw. Thx.

Userlevel 2
Badge +13

See also this workspace example: xmlupdater-insert-nodes-example.fmw (FME 2017.0.1.1)

Hello @sdave​ , xmlupdater-insert-nodes-example.fmw should be available for download now. Best, Kailin.

Reply