[SQL] 변수를 사용하여 SQL XML 속성 값을 가져옵니다

변수를 사용하여 SQL XML 속성 값을 가져옵니다

나는의 가치를 얻을하려는 XML 속성입니다 속성이라는 변수를 취하는 SQL 기능을 가지고있다. xmlPath 전체 XML 문자열입니다.

내 XML은 다음과 같습니다 :

<EventSpecificData>
  <Keyword>
    <Word>myWord</Word>
    <Occurences>1</Occurences>
    <Context>context</Context>
  </Keyword>
</EventSpecificData>

나는 / 키워드 / Word에서 통과에 변수를 설정 <말씀>, 그래서 값을 얻으려면 :

set @value = @xmlPath.value('(/EventSpecificData/@attribute)[1]', 'varchar(max)')

그러나, 나는 @attribute 실제로 변수 문자열을 삽입 생각하지 않습니다. 이 작업을 수행하는 또 다른 방법이 있나요?

해결법

1. ==============================

   1.여기 당신을 위해 몇 가지 해결책이 있습니다.

   여기 당신을 위해 몇 가지 해결책이 있습니다.

   샘플 데이터 :

   declare @xml xml
   set @xml = 
   '<EventSpecificData>
     <Keyword>
       <Word>myWord</Word>
       <Occurences>1</Occurences>
       <Context>context</Context>
     </Keyword>
   </EventSpecificData>'
   

   에 관계없이 부모의 말씀라는 이름의 노드에서 첫 번째 값을 가져옵니다. 사용 // 깊은 검색을 수행하고 노드 이름과 일치하도록 로컬 이름을 ()를 사용합니다.

   declare @Attribute varchar(max)
   
   set @Attribute = 'Word'
   select @xml.value('(//*[local-name() = sql:variable("@Attribute")])[1]',  'varchar(max)')
   

   두 레벨의 로컬 이름 ()을 사용하여 별도의 변수에 부모 노드의 이름 및 특성을 제공한다.

   declare @Node varchar(max)
   declare @Attribute varchar(max)
   
   set @Attribute = 'Word'
   set @Node = 'Keyword'
   select @xml.value('(/EventSpecificData
                       /*[local-name() = sql:variable("@Node")]
                       /*[local-name() = sql:variable("@Attribute")])[1]',  'varchar(max)')
   

   그것은이 문제를 해결하기 위해 동적 SQL을 사용하는 초대 문자 노드 매개 변수 때문에 문자열이어야합니다. 그것은 원래의 변수 내용으로 작동하도록하기 위해이 같은 것을 볼 수 있었다.

   set @Attribute = 'Keyword/Word'
   declare @SQL nvarchar(max)
   set @SQL = 'select @xml.value(''(/EventSpecificData/'+@Attribute+')[1]'', ''varchar(max)'')'
   exec sp_executesql @SQL, N'@xml xml', @xml
   

   하지만 당신은 당신이 사용하는 경우는 전체 SQL 주입 공격에 열려 있는지 알고 있어야합니다. 일부 악독 최종 사용자는 모습이 좋아하는 속성을 문자열로 올 수 있습니다

   set @Attribute = 'Keyword/Word)[1]'', ''varchar(max)'') select @@version --'
   

   그와 함께 동적 SQL을 실행하면 당신에게 두 개의 결과 집합을 제공 할 것입니다. 선택 @@ 버전은 그냥 무해한 코드가 보여주는 것입니다하지만 거기에 훨씬 더 물건 수 있습니다.

   당신은 SQL 주입 공격을 방지하기 위해 QUOTENAME ()를 사용할 수 있습니다. 그것은 적어도 나를 만든 시도를 방지 할 수 있습니다.

   set @Attribute = 'Keyword/Word'
   set @SQL = 'select @xml.value('+quotename('(/EventSpecificData/'+@Attribute+')[1]', '''')+', ''varchar(max)'')'
   exec sp_executesql @SQL, N'@xml xml', @xml
   

   QUOTENAME () 안전을 사용하여 마지막 버전인가? ERLAND Sommarskog 저주와 동적 SQL의 축복으로이 문서에서보세요.

   인용문:

2. ==============================

   2.문자열을 연결하십시오.

   문자열을 연결하십시오.

   set @value = @xmlPath.value('(/EventSpecificData/' + @attribute + ')[1]', 'varchar(max)')
   

   업데이트 답 :

   작업을 CASE'ing하자의 시도.

   SELECT @value = CASE @attribute
           WHEN 'word' THEN [word]
           WHEN 'occurrence' THEN [occurrence]
           WHEN 'context' THEN [context]
           END AS [value]
   FROM
   (
       SELECT x.u.value('(/EventSpecificData/Keyword/Word)[1]', 'varchar(max)') AS [word]
           , x.u.value('(/EventSpecificData/Keyword/Occurrence)[1]', 'varchar(max)') AS [word]
           , x.u.value('(/EventSpecificData/Keyword/Context)[1]', 'varchar(max)') AS [word]
       FROM @xmlPath.nodes('/EventSpecificData') x(u)
   ) a
   

from https://stackoverflow.com/questions/9231411/get-sql-xml-attribute-value-using-variable by cc-by-sa and MIT license