복붙노트

[SQL] XML 경로 사용할 때 어떻게 중첩 된 쿼리에서 중복 네임 스페이스를 제거합니까

SQL

XML 경로 사용할 때 어떻게 중첩 된 쿼리에서 중복 네임 스페이스를 제거합니까

기본 네임 스페이스를 선언 할 XML 경로 및 WITH XMLNAMESPACES를 사용하는 경우, 나는 XML에 사용, 나는 온라인 몇 가지 솔루션을 우연히 발견했다고 중첩 된 쿼리에 대한 최상위 노드에 중복 된 네임 스페이스 선언을 얻을 것이다,하지만 난 완전히 확신하지 ...

여기에 완벽한 예입니다

/*
drop table t1
drop table t2
*/
create table t1 ( c1 int, c2 varchar(50))
create table t2 ( c1 int, c2 int, c3 varchar(50))
insert t1 values 
(1, 'Mouse'),
(2, 'Chicken'),
(3, 'Snake');
insert t2 values
(1, 1, 'Front Right'),
(2, 1, 'Front Left'),
(3, 1, 'Back Right'),
(4, 1, 'Back Left'),
(5, 2, 'Right'),
(6, 2, 'Left')



;with XmlNamespaces( default 'uri:animal')
select 
    a.c2 as "@species"
    , (select l.c3 as "text()" 
       from t2 l where l.c2 = a.c1 
       for xml path('leg'), type) as "legs"
from t1 a
for xml path('animal'), root('zoo')

가장 좋은 방법은 무엇입니까?

해결법

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

    1.내가 제대로 이해 한 경우,이 같은 쿼리에서 볼 수있는 행동을 언급된다 :

    내가 제대로 이해 한 경우,이 같은 쿼리에서 볼 수있는 행동을 언급된다 :

    DECLARE @Order TABLE (
      OrderID INT, 
      OrderDate DATETIME)
    
    DECLARE @OrderDetail TABLE (
      OrderID INT, 
      ItemID VARCHAR(1), 
      ItemName VARCHAR(50), 
      Qty INT)
    
    INSERT @Order 
    VALUES 
    (1, '2010-01-01'),
    (2, '2010-01-02')
    
    INSERT @OrderDetail 
    VALUES 
    (1, 'A', 'Drink',  5),
    (1, 'B', 'Cup',    2),
    (2, 'A', 'Drink',  2),
    (2, 'C', 'Straw',  1),
    (2, 'D', 'Napkin', 1)
    
    ;WITH XMLNAMESPACES('http://test.com/order' AS od) 
    SELECT
      OrderID AS "@OrderID",
      (SELECT 
         ItemID AS "@od:ItemID", 
         ItemName AS "data()" 
       FROM @OrderDetail 
       WHERE OrderID = o.OrderID 
       FOR XML PATH ('od.Item'), TYPE)
    FROM @Order o 
    FOR XML PATH ('od.Order'), TYPE, ROOT('xml')
    

    어느 다음과 같은 결과를 제공합니다 :

    <xml xmlns:od="http://test.com/order">
      <od.Order OrderID="1">
        <od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
        <od.Item xmlns:od="http://test.com/order" od:ItemID="B">Cup</od.Item>
      </od.Order>
      <od.Order OrderID="2">
        <od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
        <od.Item xmlns:od="http://test.com/order" od:ItemID="C">Straw</od.Item>
        <od.Item xmlns:od="http://test.com/order" od:ItemID="D">Napkin</od.Item>
      </od.Order>
    </xml>
    

    당신이 말했듯이, 네임 스페이스는 하위 쿼리의 결과에 반복됩니다.

    그것을 변경에 투표 할 수있는 옵션이 있지만이 문제는 devnetnewsgroup (지금 소멸 웹 사이트)에 대화에 따라 기능입니다.

    내 제안 된 솔루션은 EXPLICIT XML 용으로 다시 복귀하는 것입니다 :

    SELECT
      1 AS Tag,
      NULL AS Parent,
      'http://test.com/order' AS [xml!1!xmlns:od],
      NULL AS [od:Order!2],
      NULL AS [od:Order!2!OrderID],
      NULL AS [od:Item!3],
      NULL AS [od:Item!3!ItemID]
    UNION ALL
    SELECT 
      2 AS Tag,
      1 AS Parent,
      'http://test.com/order' AS [xml!1!xmlns:od],
      NULL AS [od:Order!2],
      OrderID AS [od:Order!2!OrderID],
      NULL AS [od:Item!3],
      NULL [od:Item!3!ItemID]
    FROM @Order 
    UNION ALL
    SELECT
      3 AS Tag,
      2 AS Parent,
      'http://test.com/order' AS [xml!1!xmlns:od],
      NULL AS [od:Order!2],
      o.OrderID AS [od:Order!2!OrderID],
      d.ItemName AS [od:Item!3],
      d.ItemID AS [od:Item!3!ItemID]
    FROM @Order o INNER JOIN @OrderDetail d ON o.OrderID = d.OrderID
    ORDER BY [od:Order!2!OrderID], [od:Item!3!ItemID]
    FOR XML EXPLICIT
    

    그리고이 결과를 볼 수 :

    <xml xmlns:od="http://test.com/order">
      <od:Order OrderID="1">
        <od:Item ItemID="A">Drink</od:Item>
        <od:Item ItemID="B">Cup</od:Item>
      </od:Order>
      <od:Order OrderID="2">
        <od:Item ItemID="A">Drink</od:Item>
        <od:Item ItemID="C">Straw</od:Item>
        <od:Item ItemID="D">Napkin</od:Item>
      </od:Order>
    </xml>
    
  2. ==============================

    2.절망의 시간과 시련 및 오류의 수백 후, 아래의 해결책을 마련했습니다.

    절망의 시간과 시련 및 오류의 수백 후, 아래의 해결책을 마련했습니다.

    난 그냥 하나의 xmlns 루트 노드 만에 속성을 원하는 경우, 같은 문제가 있었다. 그러나 나는 또한 많은의 하위 쿼리의에 혼자 너무 복잡했다 XML EXPLICIT 방법이 매우 어려운 질문을했다. 그래서 그래, 난 하위 쿼리에 대한 XML을 경로의 편의를 원하고 또한 내 자신의 xmlns를 설정할 수 있습니다.

    너무 좋은 때문에 나는 친절, 8킬로바이트의 대답의 코드를 빌렸다. 나는 더 나은 이해를 위해 조금 불통. 여기에 코드입니다 :

    DECLARE @Order TABLE (OrderID INT, OrderDate DATETIME)    
    DECLARE @OrderDetail TABLE (OrderID INT, ItemID VARCHAR(1), Name VARCHAR(50), Qty INT)    
    INSERT @Order VALUES (1, '2010-01-01'), (2, '2010-01-02')    
    INSERT @OrderDetail VALUES (1, 'A', 'Drink',  5),
                               (1, 'B', 'Cup',    2),
                               (2, 'A', 'Drink',  2),
                               (2, 'C', 'Straw',  1),
                               (2, 'D', 'Napkin', 1)
    
    -- Your ordinary FOR XML PATH query
    DECLARE @xml XML = (SELECT OrderID AS "@OrderID",
                            (SELECT ItemID AS "@ItemID", 
                                    Name AS "data()" 
                             FROM @OrderDetail 
                             WHERE OrderID = o.OrderID 
                             FOR XML PATH ('Item'), TYPE)
                        FROM @Order o 
                        FOR XML PATH ('Order'), ROOT('dummyTag'), TYPE)
    
    -- Magic happens here!       
    SELECT 1 AS Tag
          ,NULL AS Parent
          ,@xml AS [xml!1!!xmltext]
          ,'http://test.com/order' AS [xml!1!xmlns]
    FOR XML EXPLICIT
    
    <xml xmlns="http://test.com/order">
      <Order OrderID="1">
        <Item ItemID="A">Drink</Item>
        <Item ItemID="B">Cup</Item>
      </Order>
      <Order OrderID="2">
        <Item ItemID="A">Drink</Item>
        <Item ItemID="C">Straw</Item>
        <Item ItemID="D">Napkin</Item>
      </Order>
    </xml>
    

    당신이 @xml 만 선택하면, 당신은 dummyTag 노드 루트 포함되어 있음을 볼 것입니다. 우리가 XML EXPLICIT 쿼리에 지침으로 xmltext를 사용하여 제거 할 수 있도록 우리는 필요하지 않습니다 :

    ,@xml AS [xml!1!!xmltext]
    

    MSDN의 설명은보다 정교한 소리를하지만,하지만 실제로는 XML의 루트 노드의 내용을 선택하는 파서를 알려줍니다.

    확실하지 쿼리가 얼마나 빨리, 그러나 현재 나는 편안하고 평화롭게 코드를 찾고있는 동안 신사처럼 스카치를 마시는입니다 ...

  3. ==============================

    3.내가 본 또 다른 해결책은 임시 변수로 XML을 구축 한 후 XMLNAMESPACES 선언을 추가하는 것입니다 :

    내가 본 또 다른 해결책은 임시 변수로 XML을 구축 한 후 XMLNAMESPACES 선언을 추가하는 것입니다 :

    declare @xml as xml;
    select @xml = (
    select 
        a.c2 as "@species"
        , (select l.c3 as "text()" 
           from t2 l where l.c2 = a.c1 
           for xml path('leg'), type) as "legs"
    from t1 a
    for xml path('animal'))
    
    ;with XmlNamespaces( 'uri:animal' as an)
    select @xml for xml path('') , root('zoo');
    
  4. ==============================

    4.문제는 여기에 XML 경로를 사용할 때 직접 수동으로 네임 스페이스를 선언 할 수 있다는 사실에 의해 악화된다. SQL 서버 '의 xmlns'그들에 콜론 어떤 태그 이름로 시작하는 속성 이름을 허용하지 않습니다.

    문제는 여기에 XML 경로를 사용할 때 직접 수동으로 네임 스페이스를 선언 할 수 있다는 사실에 의해 악화된다. SQL 서버 '의 xmlns'그들에 콜론 어떤 태그 이름로 시작하는 속성 이름을 허용하지 않습니다.

    오히려 EXPLICIT 상대적으로 비우호적 인 XML을 사용하여에 의존하는 것보다, 나는 다음과 같이 문자열을 대체를하고, '은폐'네임 스페이스 정의와 참조가 처음 생성 XML하여 문제를 주변에있어 ...

    DECLARE @Order TABLE (
      OrderID INT, 
      OrderDate DATETIME)
    
    DECLARE @OrderDetail TABLE (
      OrderID INT, 
      ItemID VARCHAR(1), 
      ItemName VARCHAR(50), 
      Qty INT)
    
    INSERT @Order 
    VALUES 
    (1, '2010-01-01'),
    (2, '2010-01-02')
    
    INSERT @OrderDetail 
    VALUES 
    (1, 'A', 'Drink',  5),
    (1, 'B', 'Cup',    2),
    (2, 'A', 'Drink',  2),
    (2, 'C', 'Straw',  1),
    (2, 'D', 'Napkin', 1)
    
    declare @xml xml
    
    set @xml = (SELECT
      'http://test.com/order' as "@xxmlns..od",  -- 'Cloaked' namespace def
      (SELECT OrderID AS "@OrderID", 
        (SELECT 
          ItemID AS "@od..ItemID", 
          ItemName AS "data()" 
         FROM @OrderDetail 
         WHERE OrderID = o.OrderID 
         FOR XML PATH ('od..Item'), TYPE)
       FROM @Order o
       FOR XML PATH ('od..Order'), TYPE)
      FOR XML PATH('xml'))
    
    set @xml = cast(replace(replace(cast(@xml as nvarchar(max)), 'xxmlns', 'xmlns'),'..',':') as xml)
    
    select @xml
    

    몇 가지 지적합니다 :

  5. ==============================

    5.선언하면서 나는 비트 모두에 대한 이러한 설명을 혼동하고있어 "에 xmlns : 동물"수동으로 일을하고있다 : 다음은 실시 예 I 오픈 그래프 메타 데이터를 생성하는 쓴

    선언하면서 나는 비트 모두에 대한 이러한 설명을 혼동하고있어 "에 xmlns : 동물"수동으로 일을하고있다 : 다음은 실시 예 I 오픈 그래프 메타 데이터를 생성하는 쓴

    DECLARE @l_xml as XML;
    SELECT @l_xml = 
    (
    SELECT 'http://ogp.me/ns# fb: http://ogp.me/ns/fb# scanilike: http://ogp.me/ns/fb/scanilike#' as 'xmlns:og',
        (SELECT
            (SELECT 'og:title' as 'property', title as 'content' for xml raw('meta'), TYPE),
            (SELECT 'og:type' as 'property', OpenGraphWebMetadataTypes.name as 'content' for xml raw('meta'), TYPE),
            (SELECT 'og:image' as 'property', image as 'content' for xml raw('meta'), TYPE),
            (SELECT 'og:url' as 'property', url as 'content' for xml raw('meta'), TYPE),
            (SELECT 'og:description' as 'property', description as 'content' for xml raw('meta'), TYPE),
            (SELECT 'og:site_name' as 'property', siteName as 'content' for xml raw('meta'), TYPE),
            (SELECT 'og:appId' as 'property', appId as 'content' for xml raw('meta'), TYPE)
         FROM OpenGraphWebMetaDatas INNER JOIN OpenGraphWebMetadataTypes ON OpenGraphWebMetaDatas.type = OpenGraphWebMetadataTypes.id WHERE THING_KEY = @p_index 
         for xml path('header'), TYPE),
         (SELECT '' as 'body' for xml path(''), TYPE)
         for xml raw('html'), TYPE
    )
    
    RETURN @l_xml 
    

    예상 된 결과를 반환

    <html xmlns:og="http://ogp.me/ns# fb: http://ogp.me/ns/fb# scanilike: http://ogp.me/ns/fb/scanilike#">
    <header>
    <meta property="og:title" content="The First object"/>
    <meta property="og:type" content="scanilike:tag"/>
    <meta property="og:image" content="http://www.mygeolive.com/images/facebook/facebook-logo.jpg"/>
    <meta property="og:url" content="http://www.scanilike.com/opengraph?id=1"/>
    <meta property="og:description" content="This is the very first object created using the IOThing &amp; ScanILike software. We keep it in file for history purpose. "/>
    <meta property="og:site_name" content="http://www.scanilike.com"/>
    <meta property="og:appId" content="200270673369521"/>
    </header>
    <body/>
    </html>
    

    사람들이 비슷한 문제에 대한 웹 검색이 의지의 도움을 바랍니다. ;-)

  6. ==============================

    6.XML 경로 실제로 더 깨끗하게 일을하면 정말 좋을 것이다. @table 변수 원래 예를 재 작업 :

    XML 경로 실제로 더 깨끗하게 일을하면 정말 좋을 것이다. @table 변수 원래 예를 재 작업 :

    declare @t1 table (c1 int, c2 varchar(50));
    declare @t2 table (c1 int, c2 int, c3 varchar(50));
    insert @t1 values 
        (1, 'Mouse'),
        (2, 'Chicken'),
        (3, 'Snake');
    insert @t2 values
        (1, 1, 'Front Right'),
        (2, 1, 'Front Left'),
        (3, 1, 'Back Right'),
        (4, 1, 'Back Left'),
        (5, 2, 'Right'),
        (6, 2, 'Left');
    
    ;with xmlnamespaces( default 'uri:animal')
    select  a.c2 as "@species",
        (
            select  l.c3 as "text()"
            from    @t2 l
            where   l.c2 = a.c1
            for xml path('leg'), type
        ) as "legs"
    from @t1 a
    for xml path('animal'), root('zoo');
    

    반복 네임 스페이스 선언의 문제의 XML을 산출 :

    <zoo xmlns="uri:animal">
      <animal species="Mouse">
        <legs>
          <leg xmlns="uri:animal">Front Right</leg>
          <leg xmlns="uri:animal">Front Left</leg>
          <leg xmlns="uri:animal">Back Right</leg>
          <leg xmlns="uri:animal">Back Left</leg>
        </legs>
      </animal>
      <animal species="Chicken">
        <legs>
          <leg xmlns="uri:animal">Right</leg>
          <leg xmlns="uri:animal">Left</leg>
        </legs>
      </animal>
      <animal species="Snake" />
    </zoo>
    

    아래와 같이, 그러나 매우 성가신 복잡한 XML에 대한 될 수 있습니다 와일드 카드 네임 스페이스와 일치하는 XQuery를 사용하여 네임 스페이스 사이의 요소를 (가 elementName입니다, *) 마이그레이션 할 수 있습니다 :

    ;with xmlnamespaces( default 'http://tempuri.org/this/namespace/is/meaningless' )
    select (
        select  a.c2 as "@species",
            (
                select  l.c3 as "text()"
                from    @t2 l
                where   l.c2 = a.c1
                for xml path('leg'), type
            ) as "legs"
        from @t1 a
        for xml path('animal'), root('zoo'), type
    ).query('declare default element namespace "uri:animal";
    <zoo>
    { for $a in *:zoo/*:animal return
        <animal>
        {attribute species {$a/@species}}
        { for $l in $a/*:legs return
            <legs>
            { for $m in $l/*:leg return
                <leg>{ $m/text() }</leg>
            }</legs>
        }</animal>
    }</zoo>');
    

    어느 원하는 결과를 얻을 수 :

    <zoo xmlns="uri:animal">
      <animal species="Mouse">
        <legs>
          <leg>Front Right</leg>
          <leg>Front Left</leg>
          <leg>Back Right</leg>
          <leg>Back Left</leg>
        </legs>
      </animal>
      <animal species="Chicken">
        <legs>
          <leg>Right</leg>
          <leg>Left</leg>
        </legs>
      </animal>
      <animal species="Snake" />
    </zoo>
    
  7. from https://stackoverflow.com/questions/3242070/how-do-i-remove-redundant-namespace-in-nested-query-when-using-for-xml-path by cc-by-sa and MIT license