Special SQL statements (learning notes), which are returned in XML, are used to add and convert large batches of data.
Special SQL statements (learning notes), which are returned in XML, are used to add and convert large batches of data.
[1] Return in XML
(1) undefined attributes
Select logisticsId, logisticsName from LogisticsCompany for xml auto, elements
(2) define the attribute section
The Code is as follows:
Select top 21 AS Tag,
Null as Parent,
Title_id AS [titles! 1! Title_id],
Title AS [titles! 1! Title! Element],
Type AS [titles! 1! Type]
FROM
Titles
FOR XML EXPLICIT
[2] adding a large volume of data
The Code is as follows:
Creat procedure [dbo]. [Sp_DB_InsertBatch]
(
@ LogXml NVARCHAR (MAX ),
@ LastTime DATETIME,
@ CurrentTime DATETIME
)
AS
BEGIN
BEGIN TRAN
BEGIN TRY
-- Using OPENXML () requires a preparation action:
-- Define a document pointer @ XMLHandler, whose type is int (SP_XML_PREPAREDOCUMENT ).
-- Release it after use (SP_XML_REMOVEDOCUMENT)
DECLARE @ XMLHandler INT;
-- OPENXML () itself returns a row set and requires three parameters and a set of column ing definitions.
-- Pre-prepared document pointer @ XMLHandler, search path start point, ing flag.
EXEC SP_XML_PREPAREDOCUMENT @ XMLHandler OUTPUT, @ LogXml;
Insert into [dbo]. [DBRate]
([RateID]
, [LastModifyTime]
, [LastTime]
, [CurrentTime])
SELECT [RateID]
, [LastModifyTime]
, @ LastTime
, @ CurrentTime
From openxml (@ XMLHandler, '/DB/dbrate', 2)
WITH (RateID INT
, LastModifyTime DATETIME
)
-- Release (SP_XML_REMOVEDOCUMENT)
EXEC SP_XML_REMOVEDOCUMENT @ XMLHandler;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
[3] Conversion Type
The Code is as follows:
Select convert (int, '192. 1 ')
Select cast ('000000' as int)
Intercept and replace other characters
Select stuff ('000000', 1347097110 ,'*******')
Obtain four data records near ID 4.
Select top 4 * from Student order by abs (4-id)
Obtain select * from (select Row_Number () Over (partition by sex order by ID) as rowindex, * from Student) a where rowindex <5
Efrom Souce from Souce inner join Student on Souce. sid = Student. id
