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

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

Cause you're looping EmbeddedImages. You need: lvV.VerslagRDL.nodes('/Report/EmbeddedImages/EmbeddedImage') — siggemannen
– siggemannen, Commented May 31, 2023 at 6:32

siggemannen · Accepted Answer · 2023-05-31 06:57:37Z

1

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

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

answered May 31, 2023 at 6:57

siggemannen

10.1k3 gold badges12 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2023-05-31 09:31:48Z

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

Collectives™ on Stack Overflow

XML query values from xml nodes only return one record

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related