1. Get the number of nodes under a node in the XML type
DECLARE @xmlXMLSET @xml = '<Parameters> <parameter name = "Param_executionid" type= "Int64"/> <parameter name = "PARAM_LOADF Romsourcesp "type=" string "/> <parameter name =" Param_loadtostagetablesp "type=" string "/> <parameter N Ame = "Param_objectname" type= "String"/></parameters>';SELECT @xml. Value ('count (/parameters/parameter)','int')
2. Turn XML into table
DECLARE @xmlXMLSET @xml = '<Parameters> <parameter name = "Param_executionid" type= "Int64"/> <parameter name = "PARAM_LOADF Romsourcesp "type=" string "/> <parameter name =" Param_loadtostagetablesp "type=" string "/> <parameter N Ame = "Param_objectname" type= "String"/></parameters>';SELECTT.c.query ('.'). Value ('(//@name) [1]','varchar (+)') asA, T.c.query ('.'). Value ('(//@type) [1]','varchar (+)') asB from @xml. Nodes ('/parameters/parameter') T (c)
SQL Server->> XML method