sql - 在 SQL 中将简单的 XML 转换为表

Question

我正在尝试将以下 XML 转换为表格。

DECLARE @XMLToParse  XML;
SET @XMLToParse =   '<Employees>
                        <Senior>
                            <Emp>
                                Ravi
                            </Emp>
                            <Emp>
                                Shanker
                            </Emp>
                            <Emp>
                                Aditya
                            </Emp>
                        </Senior>
                        <Junior>
                            <Emp>
                                Roy
                            </Emp>
                            <Emp>
                                Sham
                            </Emp>
                            <Emp>
                                Divya
                            </Emp>
                        </Junior>
                      </Employees>';


DECLARE @ParsingTable  TABLE
    (Senior VARCHAR(100),Junior VARCHAR(100))

INSERT
INTO    @ParsingTable
    (Senior)
SELECT  xmlData.A.value('.', 'VARCHAR(100)') AS Senior

FROM    @XMLToParse.nodes('Employees/Senior/Emp') as xmlData(A)

select * from @ParsingTable

我正在尝试创建如下所示的表格:

Senior     Junior
-----------------
Ravi       Roy
Shanker    Sham
Aditya     Divya

上面的代码给了我第一列，但我不知道如何插入第二列。

Answer 1

我相信有很多方法可以通过使用cross apply来获得您想要的结果，其中之一方法一

DECLARE @ParsingTable  TABLE
    (Senior VARCHAR(100),Junior VARCHAR(100))

INSERT
INTO    @ParsingTable
    (Senior,Junior)
SELECT  A.value('.', 'VARCHAR(100)') AS Senior,
        B.value('.', 'VARCHAR(100)') AS Junior
FROM    @XMLToParse.nodes('Employees/Senior/Emp') as xmlDataSenior(A) CROSS APPLY
        @XMLToParse.nodes('Employees/Junior/Emp') as xmlDataJunior(B)

注意:这会给你重复的记录

方法 2:从这个方法你只能得到一条记录......第一条记录[1]，第二条记录[2]，第三条记录[3]等等......................

SELECT  
    @XMLToParse.value('(Employees/Senior/Emp/text())[1]','VARCHAR(100)') AS Senior,
    @XMLToParse.value('(Employees/Senior/Emp/text())[1]','VARCHAR(100)') AS Junior

方法三:你可以通过这个技巧得到你想要的结果

使用 Row_Number 制作 2 个公用表表达式，一个用于高级，一个用于初级，并通过第一个 cte 的 row_number 和第二个 cte 连接这两个 cte这是你的完整代码

     DECLARE @XMLToParse  XML;
     SET @XMLToParse =   '<Employees>
                        <Senior>
                            <Emp>
                                Ravi
                            </Emp>
                            <Emp>
                                Shanker
                            </Emp>
                            <Emp>
                                Aditya
                            </Emp>
                        </Senior>
                        <Junior>
                            <Emp>
                                Roy
                            </Emp>
                            <Emp>
                                Sham
                            </Emp>
                            <Emp>
                                Divya
                            </Emp>
                        </Junior>
                      </Employees>';


DECLARE @ParsingTable  TABLE
    (Senior VARCHAR(1000),Junior VARCHAR(100))


;with cte as
(
SELECT  A.value('.', 'VARCHAR(100)') AS Senior,
        ROW_NUMBER() OVER(ORDER BY  A.value('.', 'VARCHAR(100)') DESC) AS SeniorRowNo

FROM    @XMLToParse.nodes('Employees/Senior/Emp') as xmlDataSenior(A)
)

 , cte2 as
 ( 
        SELECT  B.value('.', 'VARCHAR(100)') AS Junior,
        ROW_NUMBER() OVER(ORDER BY  B.value('.', 'VARCHAR(1000)') DESC) AS JuniorRowNo
         FROM
        @XMLToParse.nodes('Employees/Junior/Emp') as xmlDataJunior(B)   
 )

 INSERT INTO    @ParsingTable(Senior,Junior)
 Select cte.Senior,cte2.Junior
 From cte inner join cte2 on cte.SeniorRowNo= cte2.JuniorRowNo
 select * from @ParsingTable

SQLFIDDLE METHOD 3

输出:

  Senior     Junior
-----------------
Shanker    Sham
Ravi       Roy
Aditya     Divya