SQL Server XML 열의 값을 쿼리하려면 어떻게 해야 합니까?

SQL Server XML 열의 값을 쿼리하려면 어떻게 해야 합니까?

다음 XML이 XML 열에 저장되어 있습니다(호출처:RolesSQL Server 데이터베이스에 있습니다.

<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>

특정 역할이 있는 행을 모두 나열하고 싶습니다.이 역할은 매개 변수로 전달되었습니다.

select
  Roles
from
  MyTable
where
  Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

당신의 칼럼이 그렇지 않은 경우XML변환이 필요합니다.다른 구문을 사용하여 XML 데이터의 특정 속성을 조회할 수도 있습니다.여기 예가 있습니다.

데이터 열에 다음과 같은 항목이 있다고 가정합니다.

<Utilities.CodeSystems.CodeSystemCodes iid="107" CodeSystem="2" Code="0001F" CodeTags="-19-"..../>

...그리고 당신은 오직 원하는것만CodeSystem = 2그러면 다음과 같은 쿼리가 생성됩니다.

select 
  [data] 
from
  [dbo].[CodeSystemCodes_data]
  
where
  CAST([data] as XML).value('(/Utilities.CodeSystems.CodeSystemCodes/@CodeSystem)[1]', 'varchar(max)') = '2'

다음 페이지에서는 T-SQL에서 XML을 쿼리하는 방법에 대해 자세히 설명합니다.

t-sql을 사용한 XML 필드 쿼리

SQL Server에서의 XML 데이터 평탄화

편집

조금 더 가지고 놀았더니 CROSS APPLY를 사용한 놀라운 쿼리가 나왔습니다.모든 행(역할)에서 동일한 표현식에 입력한 값을 검색합니다.

다음 표 구조가 지정됩니다.

create table MyTable (Roles XML)

insert into MyTable values
('<root>
   <role>Alpha</role>
   <role>Gamma</role>
   <role>Beta</role>
</root>')

다음과 같이 조회할 수 있습니다.

select * from 

(select 
       pref.value('(text())[1]', 'varchar(32)') as RoleName
from 
       MyTable CROSS APPLY

       Roles.nodes('/root/role') AS Roles(pref)
)  as Result

where RoleName like '%ga%'

SQL Fielen은 http://sqlfiddle.com/#!18/dc4d2/1/0 에서 확인할 수 있습니다.

declare @T table(Roles xml)

insert into @T values
('<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>')

declare @Role varchar(10)

set @Role = 'Beta'

select Roles
from @T
where Roles.exist('/root/role/text()[. = sql:variable("@Role")]') = 1

조회가 다음과 같이 기능하도록 하려면where col like '%Beta%'사용할 수 있습니다.contains

declare @T table(Roles xml)

insert into @T values
('<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>')

declare @Role varchar(10)

set @Role = 'et'

select Roles
from @T
where Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1

필드 이름이 Roles이고 테이블 이름이 table1인 경우 다음을 사용하여 검색할 수 있습니다.

DECLARE @Role varchar(50);
SELECT * FROM table1
WHERE Roles.exist ('/root/role = sql:variable("@Role")') = 1

기억하기 쉬운 간단한 작업을 생각해 냈습니다:-)

select * from  
(select cast (xmlCol as varchar(max)) texty
 from myTable (NOLOCK) 
) a 
where texty like '%MySearchText%'

다음을 수행할 수 있습니다.

declare @role varchar(100) = 'Alpha'
select * from xmltable where convert(varchar(max),xmlfield) like '%<role>'+@role+'</role>%'

분명히 이것은 약간 엉터리이고 나는 어떤 공식적인 해결책에도 그것을 추천하지 않을 것이다.단, SQL Server Management Studio for SQL Server 2012의 XML 열에 대한 애드혹 쿼리를 수행할 때는 이 기술이 매우 유용합니다.

유용한 힌트입니다.SQL Server XML 열의 값 쿼리(네임스페이스가 있는 XML)

예.

Table [dbo].[Log_XML] contains columns Parametrs (xml),TimeEdit (datetime)

예: 파라미터의 XML:

<ns0:Record xmlns:ns0="http://Integration"> 
<MATERIAL>10</MATERIAL> 
<BATCH>A1</BATCH> 
</ns0:Record>

예: 쿼리:

select
 Parametrs,TimeEdit
from
 [dbo].[Log_XML]
where
 Parametrs.value('(//*:Record/BATCH)[1]', 'varchar(max)') like '%A1%'
 ORDER BY TimeEdit DESC

아래 문을 사용하여 SQL 테이블의 XML 값을 검색했습니다.

with xmlnamespaces(default 'http://test.com/2008/06/23/HL.OnlineContract.ValueObjects')
select * from (
select
            OnlineContractID,
            DistributorID,
            SponsorID,
    [RequestXML].value(N'/OnlineContractDS[1]/Properties[1]/Name[1]', 'nvarchar(30)') as [Name]
   ,[RequestXML].value(N'/OnlineContractDS[1]/Properties[1]/Value[1]', 'nvarchar(30)') as [Value]
     ,[RequestXML].value(N'/OnlineContractDS[1]/Locale[1]', 'nvarchar(30)') as [Locale]
from [OnlineContract]) as olc
where olc.Name like '%EMAIL%' and olc.Value like '%EMAIL%' and olc.Locale='UK EN'

태그 전체를 조회하거나 특정 값만 조회할 수 있습니다.여기서는 xml 네임스페이스에 와일드카드를 사용합니다.

declare @myDoc xml
set @myDoc = 
'<Root xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://stackoverflow.com">
    <Child>my value</Child>
 </Root>'

select @myDoc.query('/*:Root/*:Child') -- whole tag
select @myDoc.value('(/*:Root/*:Child)[1]', 'varchar(255)') -- only value

"Alpha" 이외의 다른 노드를 찾으려는 경우 쿼리는 다음과 같습니다.

select Roles from MyTable where Roles.exist('(/*:root/*:role[contains(.,"Beta")])') = 1

언급URL : https://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column