想詢問 Select XML 節點能否在一個陳述式中達成?

sqlserver openxml sqlserver2017

cucat 2020-12-25 17:10:43 ‧ 1658 瀏覽

分享至

如標題所述，詳情可看程式中的註解~

	DECLARE @pi_sXMLData AS NVARCHAR(MAX)
	DECLARE @objXML AS XML
    DECLARE @bIsAGoodCompany AS BIT
    DECLARE @bIsASmallCompany AS BIT
    
	SET @pi_sXMLData=N'
	<Root>
		<Lavel1>
			<BasicInfo EmpCHName="王大維"></BasicInfo>
			<DetailInfo CompanyCHName="XX公司中文名稱">
			  <IsAGoodCompany>Y</IsAGoodCompany>
			  <IsASmallCompany>N</IsASmallCompany>
			</DetailInfo>
		</Lavel1>
	</Root>'
	
	SET @objXML = CAST(@pi_sXMLData as xml)
	
	SELECT 
		@bIsAGoodCompany = 
            CASE DetailInfoXML.Col.value('.', 'varchar') 
            When 'Y' Then 1 When 'N' Then 0 Else null End
	FROM @objXML.nodes('/Root/Lavel1/DetailInfo/IsAGoodCompany') AS DetailInfoXML(Col)
   
   -- 這邊要進行兩次的 Select 去賦予兩個變數值
   -- 有沒有辦法讓他在一個 Select 中就做完呢?
	SELECT 
		@bIsASmallCompany = 
            CASE DetailInfoXML.Col.value('.', 'varchar') 
            When 'Y' Then 1 When 'N' Then 0 Else null End
	FROM @objXML.nodes('/Root/FormInfo/DetailInfo/IsASmallCompany') AS DetailInfoXML(Col)
    
    SELECT 
        @bIsAGoodCompany as AGoodCompany,
        @bIsASmallCompany as ASmallCompany

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

w4560000

iT邦研究生 5 級 ‧ 2020-12-25 17:52:52

最佳解答

這樣呢

DECLARE @pi_sXMLData AS NVARCHAR(MAX)
	DECLARE @objXML AS XML
    DECLARE @bIsAGoodCompany AS BIT
    DECLARE @bIsASmallCompany AS BIT
    
	SET @pi_sXMLData=N'
	<Root>
		<Lavel1>
			<BasicInfo EmpCHName="王大維"></BasicInfo>
			<DetailInfo CompanyCHName="XX公司中文名稱">
			  <IsAGoodCompany>Y</IsAGoodCompany>
			  <IsASmallCompany>N</IsASmallCompany>
			</DetailInfo>
		</Lavel1>
	</Root>'
	
	SET @objXML = CAST(@pi_sXMLData as xml)
	
	select @bIsAGoodCompany = case DetailInfoXML.Col.query('./IsAGoodCompany').value('.', 'varchar')
								When 'Y' Then 1 When 'N' Then 0 Else null End,
		   @bIsASmallCompany = case DetailInfoXML.Col.query('./IsASmallCompany').value('.', 'varchar')
								When 'Y' Then 1 When 'N' Then 0 Else null End
	FROM @objXML.nodes('/Root/Lavel1/DetailInfo') AS DetailInfoXML(Col)
    
    SELECT 
        @bIsAGoodCompany as AGoodCompany,
        @bIsASmallCompany as ASmallCompany