CODEWITHSUNDEEP
sql-serveropenxml
Developer
Developer

Reputation: 8646

How to split string from XML content and get the required value

Hello all I am converting an xml content and inserting it to a table variable as follows

DECLARE @iDoc int

SET @XMLData = '<NewDataSet>
  <Table>
    <DataId>2324205.3933251.7336404</DataId>
    <IsVisible>true</IsVisible>
    <Notes />
    <Marks>85.5</Marks>
   </Table>
   </NewDataSet>' 

EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLData 

SELECT DataId FROM OPENXML(@idoc, 'NewDataSet/Table', 1)
WITH   (DataId NVARCHAR(250) 'DataId')```

I would like to split the dot value and retrieve the the first value, can some one help me how to can I do that with in XML

IsVisible is a bit filed, Marks is deicmal like that I will have

Upvotes: 0

Views: 369

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22293

Starting from SQL Server 2005 onwards, it is better to use XQuery language, based on the w3c standards, while dealing with the XML data type. Microsoft proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases. It is strongly recommended to re-write your SQL and switch it to XQuery.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Notes NVARCHAR(MAX));
INSERT INTO @tbl (Notes) VALUES
(N'<NewDataSet>
    <Table>
        <DataId>2324205.3933251.7336404</DataId>
    </Table>
</NewDataSet>');
-- DDL and sample data population, end

WITH rs AS
(
    SELECT * 
        , TRY_CAST(Notes AS XML).value('(/NewDataSet/Table/DataId/text())[1]', 'VARCHAR(MAX)') AS x
    FROM @tbl
)
SELECT * 
    , LEFT(x, CHARINDEX('.', x) - 1) AS [After]
    , PARSENAME(x, 3) AS [After2]
FROM rs;

Output

+-------------------------+---------+
|         Before          |  After  |
+-------------------------+---------+
| 2324205.3933251.7336404 | 2324205 |
+-------------------------+---------+

Upvotes: 1

Related Questions