Special SQL statements (learning notes)

[1] Return in XML
(1) undefined attributes
Select logisticsId, logisticsName from LogisticsCompany for xml auto, elements
(2) define the attribute section
Copy codeThe 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
Copy codeThe 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
Copy codeThe 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