Using XQuery to create data table-CodePudding

I'm trying to format some data for a character generator. I've already got the data coming from a UDP stream into the CG as XML but now it needs to have some XQuery stuff run on it to format it. Here is an example of the data I have:

        <root>
      <element>
        <L></L>
      </element>
      <element>
        <L>0</L>
        <Name>Diana Prahoveanu</Name>
        <Club>Essex</Club>
        <Time>40</Time>
        <P>10</P>
        <Lap>C</Lap>
      </element>
      <element>
        <L></L>
      </element>
      <element>
        <L>1</L>
        <Name>Emily Cocksedge</Name>
        <Club>Hertford</Club>
        <Time>35.85</Time>
        <P>1</P>
        <Lap>F</Lap>
      </element>
      <element>
        <L></L>
      </element>

I don't know what kind of XQuery code is required to format this into its 6 columns.

Thanks!

Now, if I add some data back to the start of the XML. it still only shows the 6 columns.

<root>
  <element>
    <col1>S604</col1>
    <col2>Dual in the Pool</col2>
    <col3>Top Club(Evt.5),USA 30, EAS 22</col3>
  </element>
  <element>
    <col1></col1>
  </element>
  <element>
    <col1>Event 101 Womens Open 50m Freestyle Heat 4</col1>
  </element>
  <element>
    <col1></col1>
  </element>
  <element>
    <col1>49</col1>
  </element>
  <element>
    <col1></col1>
  </element>
  <element>
    <col1>L</col1>
    <col2>Name</col2>
    <col3>Club</col3>
    <col4>Time</col4>
    <col5>P</col5>
    <col6>Lap</col6>
  </element>
  <element>
    <col1></col1>
  </element>
  <element>
    <col1>0</col1>
    <col2>Diana Prahoveanu</col2>
    <col3>Essex</col3>
    <col4>40</col4>
    <col5>10</col5>
    <col6>C</col6>
    <col7/>
  </element>
  <element>
    <col1></col1>
  </element>
  <element>
    <col1>1</col1>
    <col2>Emily Cocksedge</col2>
    <col3>Hertford</col3>
    <col4>35.85</col4>
    <col5>1</col5>
    <col6>F</col6>
    <col7/>
  </element>

CodePudding user response：

According to http://chrworks.com/help/?url=doc/XMLJSONDataSource.html

it is expected that the XQuery is written so that the output will conform to the following general structure:

<row>
 <column1>value1</column1>
 <column2>value2</column2>
 ...
</row>

Assuming that the XML was in a variable called $doc and you only want to produce rows for the element that have those 6 child elements, ignoring the element that have the empty <L></L>:

for $row in $doc/root/element
where count($row/*) eq 6
return
  element {"row"} {
    for $col in $row/*
    return element {$col/local-name()} {$col/data()}
  }

Produces:

<row>
  <L>0</L>
  <Name>Diana Prahoveanu</Name>
  <Club>Essex</Club>
  <Time>40</Time>
  <P>10</P>
  <Lap>C</Lap>
</row>
<row>
  <L>1</L>
  <Name>Emily Cocksedge</Name>
  <Club>Hertford</Club>
  <Time>35.85</Time>
  <P>1</P>
  <Lap>F</Lap>
</row>