1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

DECLARE @ParameterSQL NVARCHAR(MAX)='SELECT * FROM table';

DECLARE @SQL NVARCHAR(MAX)

DECLARE @XMLString VARCHAR(MAX)

DECLARE @XML XML

DECLARE @Paramlist NVARCHAR(1000)

SET @Paramlist = N'@XML XML OUTPUT'

SET @SQL = 'WITH PrepareTable (XMLString)'

SET @SQL = @SQL + 'AS( '

SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW,TYPE,ELEMENTS'

SET @SQL = @SQL + ')'

SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'

EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT

SET @XMLString=CAST(@XML AS VARCHAR(MAX))

SELECT @XML;

SELECT @XMLString;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

CREATE PROCEDURE [dbo].[SerializeJSON] (

@XML XML,

@json_xml NVARCHAR(MAX) OUTPUT

)

AS

BEGIN

DECLARE @XMLString NVARCHAR(MAX);

SET @XMLString = CAST(@XML AS NVARCHAR(MAX));

BEGIN TRY--//开始捕捉异常

DECLARE @JSON NVARCHAR(MAX);

DECLARE @Row VARCHAR(MAX);

DECLARE @RowStart INT;

DECLARE @RowEnd INT;

DECLARE @FieldStart INT;

DECLARE @FieldEnd INT;

DECLARE @KEY VARCHAR(MAX);

DECLARE @Value VARCHAR(MAX);

DECLARE @StartRoot VARCHAR(100);

SET @StartRoot = '<row>';

DECLARE @EndRoot VARCHAR(100);

SET @EndRoot = '</row>';

DECLARE @StartField VARCHAR(100);

SET @StartField = '<';

DECLARE @EndField VARCHAR(100);

SET @EndField = '>';

SET @RowStart = CHARINDEX(@StartRoot, @XMLString, 0);

SET @JSON = '';

WHILE @RowStart>0

BEGIN

SET @RowStart = @RowStart + LEN(@StartRoot);

SET @RowEnd = CHARINDEX(@EndRoot, @XMLString, @RowStart);

SET @Row = SUBSTRING(@XMLString, @RowStart, @RowEnd - @RowStart);

SET @JSON = @JSON + '{';

--//for each row

SET @FieldStart = CHARINDEX(@StartField, @Row, 0);

WHILE @FieldStart>0

BEGIN

--//parse node key

SET @FieldStart = @FieldStart + LEN(@StartField);

SET @FieldEnd = CHARINDEX(@EndField, @Row, @FieldStart);

DECLARE @end INT = CHARINDEX('/>', @Row, @FieldStart);

IF @end<1

OR @end>@FieldEnd

BEGIN

SET @KEY = SUBSTRING(@Row, @FieldStart, @FieldEnd - @FieldStart);

SET @JSON = @JSON + '"' + @KEY + '":';

--//parse node value

SET @FieldStart = @FieldEnd + 1;

SET @FieldEnd = CHARINDEX('</', @Row, @FieldStart);

SET @Value = SUBSTRING(@Row, @FieldStart, @FieldEnd - @FieldStart);

SET @JSON = @JSON + '"' + @Value + '",';

END;

SET @FieldStart = @FieldStart + LEN(@StartField);

SET @FieldEnd = CHARINDEX(@EndField, @Row, @FieldStart);

SET @FieldStart = CHARINDEX(@StartField, @Row, @FieldEnd);

END;

IF LEN(@JSON)>0

SET @JSON = SUBSTRING(@JSON, 0, LEN(@JSON));

SET @JSON = @JSON + '},';

--// for each row

SET @RowStart = CHARINDEX(@StartRoot, @XMLString, @RowEnd);

END;

IF LEN(@JSON)>0

SET @JSON = SUBSTRING(@JSON, 0, LEN(@JSON));

--//SET @JSON = '[' + @JSON + ']';

SET @json_xml = @JSON;

END TRY--//结束捕捉异常

BEGIN CATCH--//有异常被捕获

SET @json_xml = @XMLString;

END CATCH;--//结束异常处理

END;