iT邦幫忙

0

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

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

	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 }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

2
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
看更多先前的回應...收起先前的回應...
cucat iT邦新手 5 級 ‧ 2020-12-28 09:34:04 檢舉

謝謝您~ 原來用該物件.Col.query 就可以選取到該節點呀!

cucat iT邦新手 5 級 ‧ 2020-12-28 14:34:27 檢舉

w4560000
不好意思,方便請教這樣的語法( .col.query )我可以上那裡找到說明呢?
我找了好久都沒有找到 @@

w4560000 iT邦研究生 5 級 ‧ 2020-12-28 17:04:50 檢舉
cucat iT邦新手 5 級 ‧ 2020-12-29 16:06:45 檢舉

謝謝!

我要發表回答

立即登入回答