Shortening Replace statement with RexEX?

user_277480 0
Viewed 40

Below is the SQL Query I am using in order to get some information. Within this information is an XML column. I am wanting to read this XML and parse out the needed ID inside the <> brackets. This query below does do that but I am looking for a cleaner way of doing it [if it exists]:

SELECT 
    tblAT.*, 
    tblA.*,
    tblEM.[Custom] AS fullXML, 
    REPLACE(
        REPLACE(
            CONVERT(
                VARCHAR(MAX), 
                tblEM.[Custom].query('/Ind/ABC')
            )
        , '<ABC>'
        , ''
        )
    ,'</ABC>'
    ,''
    ) AS ABC
FROM 
    ATable AS tblA 
JOIN 
    LLink AS tblL 
        ON tblL.A_AID = tblA.AID 
JOIN 
    AssetsT AS tblAT 
        ON tblAT.AID = tblL.BAID 
JOIN 
    ExternalMetadata AS tblEM 
        ON tblEM.AID = tblA.AID 
WHERE 
    tblAT.ATID = 12 
AND 
    tblA.AID = 30610 
AND 
    tblA.CreatedDate > '2021-05-11 08:58:00'

The XML strutor looks like this:

<Ind>
  <ABC>some value here</ABC>
</Ind>

The part:

REPLACE(
    REPLACE(
        CONVERT(
            VARCHAR(MAX), 
            tblEM.[Custom].query('/Individual/ABC')
        )
    , '<ABC>'
    , ''
    )
,'</ABC>'
,''
) AS ABC

is what I am wanting to replace with perhaps a simpler type of removing the <> from the beginning and the end of the XML.

I was hoping to be able to do a type of regex replace using /<[^>]*>/g in order to lessen the query length.

I am using SQL version 13.0.5103.6.

So is there any way of cleaning up the replace query area?

xml-parsing xml sql-server sql regex
1 Answers

No need for any RegEx and/or multiple REPLACE() calls.

XML date type could be easily handled by the XQuery.

Check it out

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [Custom] XML);
INSERT INTO @tbl ([Custom]) VALUES
(N'<Ind>
  <ABC>some value here</ABC>
</Ind>');
-- DDL and sample data population, end

SELECT * 
    , [Custom].value('(/Ind/ABC/text())[1]', 'varchar(30)') AS result
FROM @tbl;

Output

+----+---------------------------------------+-----------------+
| ID |                Custom                 |     result      |
+----+---------------------------------------+-----------------+
|  1 | <Ind><ABC>some value here</ABC></Ind> | some value here |
+----+---------------------------------------+-----------------+
user_35156130
Related