查詢指令搭配FOR子句輸出XML

最新推荐文章于 2024-09-19 10:37:26 发布

LeON382

最新推荐文章于 2024-09-19 10:37:26 发布

阅读量661

点赞数

文章标签： exe xml user

本文链接：https://blog.csdn.net/LeON382/article/details/1818465

版权

查詢指令搭配FOR子句輸出XML

基本語法如下:

FOR { XML { RAW | AUTO | EXPLICIT }

[ , XMLDATA ]

[ , ELEMENTS ]

[ , BINARY BASE64 ]

[ , TYPE ]

[ , ROOT[('ROOT_NAME')] ]

}

*ROOT表示在回傳的XML資料上加上根元素,同時可以指定根元素名稱.
為了方便做比較使用如下基本的查詢語句查出資料:
SELECT TOP(2) [EXE_ID]
      ,[EXE_NAME]
      ,[EXE_PSSWORD]
      ,[EXE_SEX]
      ,[EXE_ADDRESS]
      ,[EXE_NUMBER]

    FROM [EXE].[dbo].[EXE_UserInfo]

執行結果如下:
______________________________________________________________________________
     --------------------------------------------------------------
EXE_ID      EXE_NAME               EXE_PSSWORD                 EXE_SEX        EXE_ADDRESS           EXE_NUMBER
10002      LiuXiaoXiangaa           password                                    Gril             蘆洲市           097544568784013461
10003      WanghaiTao                password                                    Boy             山東省           450903198806013461

下面演示查詢指令搭配FOR子句輸出XML

SELECT TOP(2) [EXE_ID]
      ,[EXE_NAME]
      ,[EXE_PSSWORD]
      ,[EXE_SEX]
      ,[EXE_ADDRESS]
      ,[EXE_NUMBER]

    FROM [EXE].[dbo].[EXE_UserInfo]
FOR XML RAW('USER'), ELEMENTS,ROOT('UserS')

執行結果如下:
______________________________________________________________________________
     --------------------------------------------------------------
<UserS>
<USER>
    <EXE_ID>10002</EXE_ID>
    <EXE_NAME>LiuXiaoXiangaa</EXE_NAME>
    <EXE_PSSWORD>password</EXE_PSSWORD>
    <EXE_SEX>Gril</EXE_SEX>
    <EXE_ADDRESS>蘆洲市</EXE_ADDRESS>
    <EXE_NUMBER>097544568784013461</EXE_NUMBER>
</USER>
<USER>
    <EXE_ID>10003</EXE_ID>
    <EXE_NAME>WanghaiTao</EXE_NAME>
    <EXE_PSSWORD>password</EXE_PSSWORD>
    <EXE_SEX>Boy</EXE_SEX>
    <EXE_ADDRESS>山東省</EXE_ADDRESS>
    <EXE_NUMBER>450903198806013461</EXE_NUMBER>
</USER>
</UserS>

說明:
     RAW('USER')表示將資料集輸出成以USER為資料列元素名稱.
     ROOT('UserS')表示加入根元素形成完整的XML文件