Sample of XML date type

as long as the xml can be converted into table, thing becomes familiar and simpler

--1.orange values like xxxxxx into table
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE xmlTest
@msg xml
AS
BEGIN
declare @tmpTable table(col1 int,col2 varchar(20))
insert into @tmpTable(col1,col2)
select tbl.col.value('./abc[1]','int'),tbl.col.value('./d[1]','varchar(20)')
from @msg.nodes('/test') as tbl(col)
select * from @tmpTable
END
GO

exec xmlTest '12345testing88888test888'

--2.orange values like into table
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE xmlTest
@msg xml
AS
BEGIN
declare @tmpTable table(col1 int,col2 varchar(20))
insert into @tmpTable(col1,col2)
select tbl.col.value('@abc','int'),tbl.col.value('@d','varchar(20)')
from @msg.nodes('/test') as tbl(col)
select * from @tmpTable
END
GO

exec xmlTest exec xmlTest ''

--accept string instead
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE xmlTest
@msgStr varchar(MAX)
AS
BEGIN
declare @msg xml
set @msg=@msgStr
CREATE PROCEDURE xmlTest
@msgStr varchar(MAX)
AS
BEGIN
declare @msg xml
set @msg=@msgStr
declare @tmpTable table(col1 int,col2 varchar(20))
insert into @tmpTable(col1,col2)
select tbl.col.value('@abc','int'),tbl.col.value('@d','varchar(20)')
from @msg.nodes('/test') as tbl(col)
select * from @tmpTable
END
GO