XML value: Select One Typed Scalar

I use XML value when the query needs one typed scalar. The XQuery path selects the source, and the SQL type defines the returned contract. Selecting the first item doesn’t establish uniqueness.

Keep the attribute and the result together

The example reads the id attribute from three small XML documents. Their expected integer values are seven, zero and negative four. The original document text remains beside each extracted ItemId.

I keep the inputs visible because conversion success alone doesn’t explain the business rule. A negative identifier can be valid as an int while violating an application’s allowed range. The method’s declared output type establishes conversion, not the meaning of every resulting number.

WITH Documents AS
(
 SELECT CaseId, CAST(XmlText AS nvarchar(200)) AS XmlText,
        CAST(XmlText AS xml) AS XmlDocument
 FROM (VALUES (1,N'<r id="7"/>'),(2,N'<r id="0"/>'),
              (3,N'<r id="-4"/>')) v(CaseId,XmlText)
)
SELECT CaseId, XmlText,
       XmlDocument.value('(/r/@id)[1]','int') AS ItemId
FROM Documents
ORDER BY CaseId;

Declare a singleton path

The value method requires an XQuery result with at most one value. The [1] is needed to satisfy static typing, even when a supplied document has one matching attribute. The example uses that explicit form.

The parentheses apply first-item selection to the complete path result. I’d keep them when explaining the expression to another reviewer. They make the selected sequence clear instead of leaving the reader to infer which step receives the singleton restriction.

Do not confuse first with unique

The [1] predicate selects the first item from the path result. It doesn’t prove that the original document contains only one matching item. That distinction matters when a feed can repeat elements.

If uniqueness is part of the application’s contract, validate it separately. I’d avoid treating a successfully extracted value as evidence that no additional candidates existed. Choosing one value can conceal duplicate input just as a first-row relational query can conceal tied rows.

Review conversion before using the scalar

The second argument is the SQL type string int. The value method uses SQL conversion rules implicitly. All supplied attributes contain valid integer text, so the query doesn’t execute a deliberately failing conversion.

I’d test the actual accepted vocabulary before extracting production values. A label that looks numeric can still exceed the destination range or contain unexpected text. An explicit type is useful, but it doesn’t make every possible source value fit that type.

Separate extraction from validation

The query reports the scalar without asserting that an id is positive or references another record. It changes no XML and creates no table. Its purpose is to expose a small extraction contract.

I wouldn’t name the output ValidId unless a separate rule established that validity. Keep extraction, allowed-range checks and relationship checks readable as distinct steps. That separation helps a reviewer identify whether a rejection concerns XML structure, conversion or business meaning.

Carry the complete contract into a real query

These documents have no namespace and one direct attribute path. Real XML can require namespace declarations and a different singleton expression. Copying only the method call without the surrounding path assumptions can change its meaning.

I’d retain representative documents and compare the full typed outputs on the target instance. The three small rows demonstrate ordinary integer extraction, while missing attributes and repeated elements need their own stated policy. A successful baseline shouldn’t silently define those unresolved cases.

Check one repeated element in your own feed and see what [1] hides.

The first item is not proof of uniqueness, it is one typed scalar chosen by a path.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.