Query on XML column with space condition-CodePudding

I have table in that one column is XML column.

Table1:

Column1   Column2
1         XML value

Here I need to find the attribute values where it has leading spaces , for example .

This is the XML column [Column2 in the above table], here BankAccountNumber tag has leading space , so I need to write a query to find all the records in the table wherever it has leading spaces, could you please help me.

<PaymentMethodDetails>
    <EFtDetails>
        <routingNumber>5575776567</routingNumber>
        <BankAccountNumber>1234 </BankAccountNumber>
    </EFtDetails>
</PaymentMethodDetails>

CodePudding user response：

Unfortunately, SQL Server does not support starts-with and ends-with functions. But we can get around that with substring

substring(., 1, 1) = " "

To look for a trailing space rather than a leading space, instead use

substring(., string-length(.), 1) = " "

To find any BankAccountNumber nodes starting with a space, you can use exist

SELECT *
FROM Table1 t1
WHERE t1.Column2.exist('
  /PaymentMethodDetails/EFtDetails/BankAccountNumber/text()[substring(., string-length(.), 1) = " "]
') = 1;

To find any nodes starting with a space, you can use // to include all descendant nodes

SELECT *
FROM Table1 t1
WHERE t1.Column2.exist('
  //*[text()[substring(., string-length(.), 1) = " "]]
') = 1;

To actually shred the matching nodes into separate values, you can use a combination of .nodes and .value

SELECT
  x.nod.value('local-name(.)[1]','nvarchar(max)'),
  x.nod.value('text()[1]','nvarchar(max)')
FROM Table1 t1
CROSS APPLY t1.Column2.nodes('
  //*[text()[substring(., string-length(.), 1) = " "]]
') x(nod);

db<>fiddle