XML query values from xml nodes only return one record

Question

I have used a number of sources to get to this point:

How to query values from xml nodes? and http://www.sqlfiddle.com/#!18/3e6ed/1

T-Sql xml query with namespace

How to query a specific tag from XML field data in sql?

How can I use namespaces in a SQL XML query with the "nodes" command?

How to query for Xml values and attributes from table in SQL Server?

I have the follow XML source: I have removed the actual imagedata because of size constraints

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <EmbeddedImages>
    <EmbeddedImage Name="RioTinto">
      <MIMEType>image/jpeg</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="Palweb">
      <MIMEType>image/jpeg</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="SABS1">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="SABS2">
      <MIMEType>image/jpeg</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="ISC">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="ISO_9001_ISO_14001_COL">
      <MIMEType>image/jpeg</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="GSCMPMCHeader">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="ISO_9001_ISO_14001_COL1">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="TopEmployer2019">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
  </EmbeddedImages>
</Report>

I run the following Query on it:

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
)
SELECT lvV.Name,
       reportdata.item.value('(./EmbeddedImage)[1]', 'VARCHAR(2000)') AS EmbeddedImages,
       reportdata.item.value('(./EmbeddedImage/@Name)[1]', 'VARCHAR(2000)') AS Name,
       reportdata.item.value('(./EmbeddedImage/MIMEType)[1]', 'VARCHAR(2000)') AS MIMEType,
       reportdata.item.value('(./EmbeddedImage/ImageData)[1]', 'VARCHAR(2000)') AS ImageData
FROM @LysvanVerslae AS lvV
    cross APPLY lvV.VerslagRDL.nodes('/Report/EmbeddedImages') AS reportdata(item);

Why does it only return one row as the results instead of 9. It does not matter whether I remove the value('(./ or not

Answer 1

You're looping EmbeddedImages. You need to add the repeating node like:

... 
lvV.VerslagRDL.nodes('/Report/EmbeddedImages/EmbeddedImage') AS reportdata(item); 

Answer 2

As mentioned, you are need to go down to EmbeddedImage in the .nodes call. But then you also need to remove that from the .values call, as you already went down to that level.

Further improvements:

• ./ is pointless, it's the default anyway to start from the current node.
• You should use text() in order to pull out the inner text of a node, rather than relying on implicit conversion, for performance reasons.
• @Attribute does not require [1] for the current node, because it's guaranteed unique.
• Choose better data types.

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
)
SELECT lvV.Name,
       reportdata.item.value('text()[1]', 'VARCHAR(2000)') AS EmbeddedImages,
       reportdata.item.value('@Name', 'VARCHAR(200)') AS Name,
       reportdata.item.value('(MIMEType/text())[1]', 'VARCHAR(200)') AS MIMEType,
       reportdata.item.value('(ImageData/text())[1]', 'VARCHAR(max)') AS ImageData
FROM LysvanVerslae AS lvV
cross APPLY lvV.VerslagRDL.nodes('/Report/EmbeddedImages/EmbeddedImage') AS reportdata(item);

db<>fiddle