쿼리중에 문법이 다르게 써야 하는... xml 을 사용하는 쿼리라 XQuery...

할때마다 찾는게 힘들어서 남깁니다.


xquery 전반적인 문법... 다

http://www.w3schools.com/xquery/default.asp


xquery가지고 수정, 삽입, 삭제등의 방법

http://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

 

XQuery를 사용한 XML 데이터 업데이트

 

-      Version :  SQL Server 2005,2008, 2008R2, 2012

 

XML의 값을 수정하는 방법에는 무엇이 있을까? XML 편집기 등을 이용하여 사용자가 직접 수정 할 수도 있지만 XML노드와값을 정확하게 수정하려면 XQuery를 사용하여 수정 할 수 있다.

XQuery는 구조화 되었거나 반구조화된 XML 데이터를 쿼리할 수 있는 언어이다. XQuery는 기존의XPath 쿼리 언어를 기반으로 더 나은 반복 성능 및 정렬 결과를 위한 지원이 추가 되었다.

자세한 내용은 MSDN을 참고한다.

 

[노드삽입]

삽입 키워드는 다음과 같은 구조를 사용한다.

Insert Expression1 ({as first | as last}into | after | before Expression2)

삽입 키워드를 통하여 샘플 테이블을 생성 한다.

CREATE TABLE HR_XML (ID INT IDENTITY, SALARIES XML)

GO

INSERT HR_XML VALUES(

    '<SALARIES>

      <MARKETING>

        <EMPLOYEE ID="1" TIER="4">

          <SALARY>42000</SALARY>

        </EMPLOYEE>

        <EMPLOYEE ID="2" TIER="1">

          <SALARY>52000</SALARY>

        </EMPLOYEE>

        <EMPLOYEE ID="3" TIER="4">

          <SALARY>48000</SALARY>

        </EMPLOYEE>

      </MARKETING>

    </SALARIES>

    '

)

GO

 

SELECT * FROM HR_XML

GO

 

     

 

 

<Account / >라는 새로운 노드를 생성하려면 INSERT 구문을 이용하여 수행 할 수 있다.

UPDATE HR_XML

SET Salaries.modify('insert <Accounting /> into (/Salaries)[1]')

GO

 

SELECT * FROM HR_XML

GO

 

 

 

위의 쿼리에서 보면 Salaries [1] 노드에 대해 Singleton Designation 값이 지정된 것을 확인 할 수 있다.Singleton Designation 없이 쿼리를 실행하면 오류가 발생 한다.

UPDATE HR_XML

SET Salaries.modify('insert <Accounting /> into (/Salaries)')

GO

 

 

 

[원하는위치 노드 추가]

노드를 추가하면 기존의 노드 이후에 삽입되는 것을 확인 할 수 있다. (위그림 참조). 원하는 위치에 노드를 삽입하는 방법을 알아 보자실습에서는 <Salaries> 아래 첫번째 노드에 삽입을 하여 보자. Firstinto 명령어를 이용한다.

UPDATE HR_XML

SET Salaries.modify('insert <Accounting /> as first into (/Salaries)[1]')

GO

 

select * from HR_XML

GO

 

 

 

첫번째 노드 <Account /> Employee 노드(ID, tier)를 입력 해보자노드를 입력 할 때 속성을 포함해야 한다. SingletonDesignation [2] 지정으로 <Account>노드 바로 아래 <Employee>가 삽입 되는 것을 확인 할 수 있다.

UPDATE HR_XML

SET Salaries.modify('insert <Employee ID="4" tier="4" /> into (/Salaries/Accounting)[1]')

GO

 

select * from HR_XML

GO

 

 

 

[특정노드를 식별하여 삽입]

특정 Employee의 노드에 새 노드를 삽입하여 보자. @ID 인수를 사용하여 Employee를 식별 할 수 있다실습에서는 Employee ID = 2인 노드를 찾아서 새로운 노드를삽입하였다.

UPDATE HR_XML

SET Salaries.modify('insert <Projects /> into (/Salaries/Marketing/Employee[@ID=("2")])[1]')

GO

 

select * from HR_XML

GO

 

 

 

[노드삽입과 값 삽입]

노드 삽입과 컬력션 삽입을 할 수 있다. Employee id =2의노드에 새로운 노드(Project ID = 1)과 값을 삽입한다.

UPDATE HR_XML

SET Salaries.modify('insert <Project ID="1"><Description>Organize new

    strategies</Description></Project> into

    (/Salaries/Marketing/Employee[@ID=("2")]/Projects)[1]')

GO

 

select * from HR_XML

GO

 

 

 

[다른노드 값을 참조하여 노드 삽입]

Employee id = 1의 값을 참조하여 Employee ID = 4 <Salary>노드를 추가하여보자중괄호를 사용하여 명시적으로 노드를 지정한 것을 확인 할 수 있다.

UPDATE HR_XML

SET Salaries.modify('insert

<Salary>{(/Salaries/Marketing/Employee[@ID=("1")]/Salary/text())}</Salary>

into (/Salaries/Accounting/Employee[@ID=("4")])[1]')

GO

 

select * from HR_XML

GO

 

 

 

[노드삭제]

노드 삭제는 노드 삽입보다 간단하다표현식은

Delete Expression 이다노드 마지막의 <Account />노드를 삭제 하여 보자.

UPDATE HR_XML

SET Salaries.modify('delete (/Salaries/Accounting)[2]')

GO

 

select * from HR_XML

GO

 

 

 

[값삭제]

값 삭제는 텍스트() 함수를 사용하여 삭제 할 수 있다.

UPDATE HR_XML

SET Salaries.modify('delete

(/Salaries/Marketing/Employee[@ID=("2")]/Projects/Project[@ID="1"]/Description/text())[1]')

GO

 

select * from HR_XML

GO

 

 

 

[값수정]

Exployee ID = 2<Salary> 값을 수정하여 보자.

UPDATE HR_XML

SET Salaries.modify('replace value of

(/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")')

GO

 

select * from HR_XML

GO

 

 

 

[XQuery의산술값을 이용한 수정]

모든 Exployee에 대해서<Salary>의 값이 10% 증가한 값을 수정할 수 있도록 반복문을 사용 할수 있다.

DECLARE @i INT = 1

WHILE @i <= 3

   BEGIN

 UPDATE HR_XML

 SET Salaries.modify('replace value of

 (/Salaries/Marketing/Employee[@ID=(sql:variable("@i"))]/Salary/text())[1]

 with (/Salaries/Marketing/Employee[@ID=(sql:variable("@i"))]/Salary)[1] * 1.01')

 SET @i+=1

   END

GO

 

select * from HR_XML

GO

 

 

 

[수정제한]

XQuery에서 Modify() 메소드는수정하려는 값을 SELECT와 함께 사용 할 수 없다.

SELECT Salaries.modify('replace value of

(/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1]

with ("60000")')

FROM HR_XML

GO

 

 

 

 

기존의 값을 조회하여 수정하려면 다음과 같이 변수를 이용하여 사용하여야 한다.

DECLARE @x XML

SELECT @x = Salaries FROM HR_XML

SET @x.modify('replace value of (/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")')

SELECT @x

GO

 

 

 

쿼리문에서 UPDATE 구문을 동시에 2개 사용할 수 없다별도의 업데이트문을 사용하여야 한다.

UPDATE HR_XML

SET Salaries.modify('replace value of

(/Salaries/Marketing/Employee[@ID=("2")]/Salary/text())[1] with ("60000")'),

Salaries.modify('replace value of

(/Salaries/Marketing/Employee[@ID=("1")]/Salary/text())[1] with ("60000")')

GO

 

 

 

Employee ID = 2의 값을 참조하여 Employee ID = 1의 값을 업데이트 할 수 있다.

UPDATE HR_XML

SET Salaries.modify('replace value of

(/Salaries/Marketing/Employee[@ID=("1")]/Salary/text())[1]

with (/Salaries/Marketing/Employee[@ID=("2")]/Salary)')

GO

 

select * from HR_XML

GO

 

 

 

[계층값 변경]

@ID 값을 사용하여Employee ID = 1 tier = 4 값을tier =1로 변경 할 수 있다.

UPDATE HR_XML

SET Salaries.modify('replace value of

(/Salaries/Marketing/Employee[@ID=("1")]/@tier)[1] with "1"')

GO

 

select * from HR_XML

GO

 

 

 

 

XQuery를 사용하여 XMLDML 실습을 하였다많은 XML 편지기가있지만 SSMS를 이용하여 속성값을 사용하여 정확하게 수정할 때에 매우 유용하게 사용 할 수 있을 듯하다.

 

 

출처 : http://polyphagia.egloos.com/10231978


아래 내용을 쿼리분석기에서 실행하면 찾으려는 단어가 포함된 프로시저 명을 알려준다.

단위는 데이터베이스 단위로 검색이 이루어져 특정 문자가 들어간 프로시저를 찾을때 용이하다.

 

-- 프로시저 검색
SELECT O.name
FROM   sysobjects O JOIN syscomments C
ON     O.id = C.id
WHERE  O.type = 'P' AND C.text LIKE '%찾으려는 단어%'


출처 : http://www.webmadang.net/database/database.do?action=read&boardid=4001&page=null&seq=5

[MSSQL - GROUP BY HAVING 을 이용한 중복 데이타 체크]



1 - 단일 테이블에서 GROUP BY HAVING 을 이용한 중복 데이타 체크

아래와 같은 로그인 정보를 기록하는 9개의 레코드를 가진 테이블이 있다고 가정하겠습니다. 테이블의 이름은
"loginLog_tbl" 이고 로그인 로그(log)를 기록해야 하니 당연히 중복된 데이타가 들어가 있을것입니다. 

위 테이블의 레코드를 보면 mfcchang이라는 uid가 3회나 로그인 했다는 것을 알 수 있습니다. 레코드의 갯수가 작아서
눈으로도 파악이 되지만 레코드의 갯수가 많을때는 다음과 같은 쿼리문으로 간편하게 파악 할 수도 있습니다.

SELECT uid, count(*) AS loginCount FROM loginLog_tbl GROUP BY uid ORDER BY loginCount DESC

위와 같은 쿼리를 실행해보면 GROUP BY 에 의해서 uid 컬럼의 count가 집계되고 결과는 아래와 같습니다.

 

여기에 Having 절의 제약조건을 이용해서 loginCount가 1 보다 큰것만 출력해 보겠습니다. loginCount 는 count(*)의 엘리어스 이니까, Having 절 이하는 아래와 같을 것입니다.

SELECT uid, count(*) AS loginCount FROM loginLog_tbl GROUP BY uid HAVING count(*) > 1

출력해 보면 결과는 아래와 같습니다. 아래의 결과에서 두번 즉 2회 이상 로그인한 사람은 kimchi 와 mfcchang 이라는 uid의 소유자 뿐임을 알수 있습니다.

 

어떠한 경우에서든 데이타의 중복을 확인하고자 한하면 바로 위의 쿼리문을 이용하시면 됩니다. 중복을 체크하고자 하는 
기준이 되는 컬럼만 바꾸면 될 것입니다.



2 - 두개 이상의 테이블에서 GROUP BY HAVING 을 이용한 중복 데이타 체크 

그렇다면 중복을 체크하고자 하는 컬럼이 위와 같이 테이블이 하나가 아니고 둘또는 셋이상에 걸쳐서 존재한다면 어떻게
처리하면 될까요? 테이블간에 합집합의 결과를 모두 보여주는 UNION ALL 로 테이블을 결합하시면 됩니다.

아래와 같이 로그인 정보를 기록하는 테이블이 하나 더 있고, 이 테이블은 예전에 퇴사한 선임자가 만들어 놓은 것이라고 
가정하겠습니다. 테이블의 이름은 loginLogTABLE 입니다.

 

처음에 사용했던 loginLog_tbl과 바로 위의 logLogTABLE에서 uid 컬럼의 데이타값이 2 개 이상인 것을 찾아내는
쿼리는 아래와 같습니다.

SELECT uid, count(*) AS loginCount FROM 
SELECT uid FROM loginLog_tbl UNION ALL SELECT uid FROM loginLogTABLE ) AS T
GROUP BY uid HAVING count(*) > 1

하나의 테이블을 사용할때와 틀린부분은 UNION ALL 로 해당 테이블을 묶어서 서브쿼리로 처리한다는 것뿐입니다.
결과는 아래와 같습니다. 두개의 테이블의 레코드 갯수가 몇개 없으니 눈으로도 확인 가능할 것입니다.

작성자: 다자래(mfcchang@naver.com) 
웹제작 토탈 커뮤니티 http://www.webmadang.net


COPY & PASTE 로 간편하게 사용할 수 있는 SQL Server Trobleshooting 에 필요한 쿼리들을 정리하려고 합니다. 다소 심플하며 성의없어(?) 보이는 포스팅이 이어질지 모르겠습니다만 이 모든 것이 이~편한 세상을 위한 것임으로 ... ㅋㅋㅋ 

 

 

sys.dm_exec_requests

 

SQL Server 내에서 실행 중인 각 요청에 대해 현재 처리 상태를 확인합니다. 예를 들어, 대용량 DB CHECKDB 하면서 소요 예상 시간이 궁금할 , 현재 쿼리 요청에 대한 작업 상태를 확인할 , 유용하게 적용할 있습니다. 

 

아래 예제는 Session ID 51에 대한 진행되는 명령어, 실행 상태, 진행 작업의 백분율을 확인할 수 있습니다.

이 때, Session ID 51에서는 DBCC CHECKDB 를 수행하고 있는 상태입니다.

SELECT session_id, command, status, percent_complete FROM sys.dm_exec_requests 

WHERE session_id = '51';

 

 

[실행결과]

 

session_id     command                    status                percent_complete

----------------------------------------------------------------------------------------------------------

51               DBCC TABLE CHECK      runnable            84.04607

(1개 행 적용됨)

 

 

[참고자료]

sys.dm_exec_requests

http://technet.microsoft.com/ko-kr/library/ms177648(SQL.90).aspx

 

원본 위치 <http://laigo.kr/111

+ Recent posts