Stuff 및 'For Xml Path'는 SQL Server에서 어떻게 작동합니까?

Stuff 및 'For Xml Path'는 SQL Server에서 어떻게 작동합니까?

표:

이드	이름.
1	아
1	bbb
1	ccc
1	Ddd.
1	이

필요한 출력:

이드	abc
1	aaa,bb,cccc,ddd,ee

쿼리:

SELECT ID, 
    abc = STUFF(
                 (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
               ) 
FROM temp1 GROUP BY id

이 쿼리는 제대로 작동하고 있습니다.하지만 저는 그것이 어떻게 작동하는지 또는 이것을 할 수 있는 다른 또는 짧은 방법이 있는지 설명이 필요합니다.

저는 이것을 이해하는 것이 매우 혼란스럽습니다.

작동 방식은 다음과 같습니다.

FOR XML을 사용하여 XML 요소 문자열 가져오기

쿼리 끝에 FOR XML PATH를 추가하면 PATH 인수에 포함된 요소 이름으로 쿼리 결과를 XML 요소로 출력할 수 있습니다.예를 들어, 다음 명령문을 실행하면 다음과 같습니다.

SELECT ',' + name 
              FROM temp1
              FOR XML PATH ('')

빈 문자열(FOR XML PATH(')을 전달하면 대신 다음을 얻을 수 있습니다.

,aaa,bbb,ccc,ddd,eee

STUFF를 사용하여 선행 쉼표 제거

STUFF 문은 문자 그대로 한 문자열을 다른 문자열로 "채워서" 첫 번째 문자열 내의 문자를 대체합니다.그러나 결과 값 목록의 첫 번째 문자를 제거하는 데만 사용합니다.

SELECT abc = STUFF((
            SELECT ',' + NAME
            FROM temp1
            FOR XML PATH('')
            ), 1, 1, '')
FROM temp1

의 매 변수의 STUFF다음과 같습니다.

• "채울" 문자열(이 경우 쉼표로 선행하는 이름의 전체 목록)
• 문자 삭제 및 삽입을 시작할 위치(1, 빈 문자열에 채우기)
• 삭제할 문자 수(1, 선두 쉼표)

결과는 다음과 같습니다.

aaa,bbb,ccc,ddd,eee

전체 목록을 보려면 ID에 가입하십시오.

다음으로 임시 테이블의 ID 목록에 이 목록을 추가하여 이름이 다음과 같은 ID 목록을 가져옵니다.

SELECT ID,  abc = STUFF(
             (SELECT ',' + name 
              FROM temp1 t1
              WHERE t1.id = t2.id
              FOR XML PATH (''))
             , 1, 1, '') from temp1 t2
group by id;

결과는 다음과 같습니다.

이드	이름.
1	aaa,bb,cccc,ddd,ee

이 문서에서는 연결된 값을 XML로 인코딩하지 않는 코드의 개선된 버전을 포함하여 SQL에서 문자열을 연결하는 다양한 방법에 대해 설명합니다.

SELECT ID, abc = STUFF
(
    (
        SELECT ',' + name
        FROM temp1 As T2
        -- You only want to combine rows for a single ID here:
        WHERE T2.ID = T1.ID
        ORDER BY name
        FOR XML PATH (''), TYPE
    ).value('.', 'varchar(max)')
, 1, 1, '')
FROM temp1 As T1
GROUP BY id

현재 상황을 이해하려면 내부 질문부터 시작합니다.

SELECT ',' + name
FROM temp1 As T2
WHERE T2.ID = 42 -- Pick a random ID from the table
ORDER BY name
FOR XML PATH (''), TYPE

당신이 지정하고 있기 때문입니다.FOR XML모든 행을 나타내는 XML 조각이 포함된 단일 행을 얻을 수 있습니다.

번째 열에 열각 안에 합니다.FOR XML PATH를 들어, 만약 이 를들어당, 신이만약을 가지고 .FOR XML PATH ('X')다음과 같은 XML 문서를 얻을 수 있습니다.

<X>,aaa</X>
<X>,bbb</X>
...

그러나 요소 이름을 지정하지 않았으므로 값 목록만 표시됩니다.

,aaa,bbb,...

그.value('.', 'varchar(max)')는 "특수" 문자를 XML 인코딩하지 않고 결과 XML 조각에서 값을 검색합니다.이제 다음과 같은 문자열이 표시됩니다.

',aaa,bbb,...'

그STUFF그런 다음 함수가 선행 콤마를 제거하여 다음과 같은 최종 결과를 제공합니다.

'aaa,bbb,...'

언뜻 보기에는 상당히 혼란스러워 보이지만 다른 옵션과 비교하면 성능이 상당히 우수한 경향이 있습니다.

PATH 모드는 SELECT 쿼리에서 XML을 생성하는 데 사용됩니다.

1. SELECT   
       ID,  
       Name  
FROM temp1
FOR XML PATH;  

Ouput:
<row>
<ID>1</ID>
<Name>aaa</Name>
</row>

<row>
<ID>1</ID>
<Name>bbb</Name>
</row>

<row>
<ID>1</ID>
<Name>ccc</Name>
</row>

<row>
<ID>1</ID>
<Name>ddd</Name>
</row>

<row>
<ID>1</ID>
<Name>eee</Name>
</row>

출력은 요소 중심 XML로, 결과 행 집합의 각 열 값이 행 요소로 감싸집니다.SELECT 절은 열 이름에 대한 별칭을 지정하지 않으므로 생성된 자식 요소 이름은 SELECT 절의 해당 열 이름과 동일합니다.

행 집합의 각 행에 대해 태그가 추가됩니다.

2.
SELECT   
       ID,  
       Name  
FROM temp1
FOR XML PATH('');

Ouput:
<ID>1</ID>
<Name>aaa</Name>
<ID>1</ID>
<Name>bbb</Name>
<ID>1</ID>
<Name>ccc</Name>
<ID>1</ID>
<Name>ddd</Name>
<ID>1</ID>
<Name>eee</Name>

2단계: 길이가 0인 문자열을 지정하면 래핑 요소가 생성되지 않습니다.

3. 

    SELECT   

           Name  
    FROM temp1
    FOR XML PATH('');

    Ouput:
    <Name>aaa</Name>
    <Name>bbb</Name>
    <Name>ccc</Name>
    <Name>ddd</Name>
    <Name>eee</Name>

4. SELECT   
        ',' +Name  
FROM temp1
FOR XML PATH('')

Ouput:
,aaa,bbb,ccc,ddd,eee

4단계에서는 값을 연결합니다.

5. SELECT ID,
    abc = (SELECT   
            ',' +Name  
    FROM temp1
    FOR XML PATH('') )
FROM temp1

Ouput:
1   ,aaa,bbb,ccc,ddd,eee
1   ,aaa,bbb,ccc,ddd,eee
1   ,aaa,bbb,ccc,ddd,eee
1   ,aaa,bbb,ccc,ddd,eee
1   ,aaa,bbb,ccc,ddd,eee


6. SELECT ID,
    abc = (SELECT   
            ',' +Name  
    FROM temp1
    FOR XML PATH('') )
FROM temp1 GROUP by iD

Ouput:
ID  abc
1   ,aaa,bbb,ccc,ddd,eee

6단계에서는 ID별로 날짜를 그룹화합니다.

STUFF( source_string, start, length, add_string ) 매개 변수 또는 Arguments source_string 수정할 소스 문자열. source_string에서 길이 문자를 삭제할 위치를 시작한 다음 add_string을 삽입합니다.length source_string에서 삭제할 문자 수입니다.add_string 시작 위치에서 source_string에 삽입할 문자의 순서입니다.

SELECT ID,
    abc = 
    STUFF (
        (SELECT   
                ',' +Name  
        FROM temp1
        FOR XML PATH('')), 1, 1, ''
    )
FROM temp1 GROUP by iD

Output:
-----------------------------------
| Id        | Name                |
|---------------------------------|
| 1         | aaa,bbb,ccc,ddd,eee |
-----------------------------------

Azure SQL Database 및 SQL Server(2017년부터)에는 이 정확한 시나리오를 처리할 수 있는 매우 새로운 기능이 있습니다.XML/STUFF 방법으로 수행하려는 작업에 대한 네이티브 공식 방법이 될 것으로 생각합니다.예:

select id, STRING_AGG(name, ',') as abc
from temp1
group by id

STRING_AGG - https://msdn.microsoft.com/en-us/library/mt790580.aspx

편집: 처음 이 글을 게시할 때 SQL Server 2016에 대해 언급했습니다. 이 글은 잠재적인 기능에 포함될 것이라고 생각했기 때문입니다.제가 잘못 기억했거나 뭔가가 바뀌었거나, 버전을 수정하는 제안된 편집에 감사드립니다.또한, 저를 최종 선택지로 끌어들인 다단계 검토 과정에 대해 꽤 인상적이었고 완전히 알지 못했습니다.

for xml path만약 우리가 어떤 가치를 정의한다면,[ for xml path('ENVLOPE') ]그러면 각 행에 다음 태그가 추가됩니다.

<ENVLOPE>
</ENVLOPE>

SELECT ID, 
    abc = STUFF(
                 (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
               ) 
FROM temp1 GROUP BY id

위 쿼리에서 STUFF 기능은 첫 번째 쉼표를 제거하는 데 사용됩니다.(,) 문자열 "xml"에서(,aaa,bbb,ccc,ddd,eee)그러면 그것은 될 것입니다.(aaa,bbb,ccc,ddd,eee).

그리고.FOR XML PATH('')단순하게 열 데이터를 변환합니다.(,aaa,bbb,ccc,ddd,eee)문자열이지만 PATH에서는 '을(를) 전달하고 있으므로 XML 태그를 만들지 않습니다.

그리고 마지막에는 ID 열을 사용하여 레코드를 그룹화했습니다.

디버깅을 하고 마침내 '채워진' 쿼리를 정상적인 방법으로 되돌렸습니다.

간단히

select * from myTable for xml path('myTable')

디버깅하는 트리거에서 로그 테이블에 쓸 테이블의 내용을 제공합니다.

Declare @Temp As Table (Id Int,Name Varchar(100))
Insert Into @Temp values(1,'A'),(1,'B'),(1,'C'),(2,'D'),(2,'E'),(3,'F'),(3,'G'),(3,'H'),(4,'I'),(5,'J'),(5,'K')
Select X.ID,
stuff((Select ','+ Z.Name from @Temp Z Where X.Id =Z.Id For XML Path('')),1,1,'')
from @Temp X
Group by X.ID

언급URL : https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server