[SQL] 나는 SQL 서버의 XML 값에서 요소 이름의 목록을 얻을 수있는 방법

나는 SQL 서버의 XML 값에서 요소 이름의 목록을 얻을 수있는 방법

나는 SQL 서버 2K8의 XML 열이있는 테이블이 있습니다. 다음 SQL은 일부 XML을 검색 :

SELECT TOP 1 my_xml_column FROM my_table

의 그것이 나에게 다음과 같은 XML을 반환한다고 가정 해 봅시다

<a>
  <b />
  <c>
    <d />
    <d />
    <d />
  </c>
</a>

내가 좀하고 싶습니다 것은

/a
/a/b
/a/c
/a/c/d
/a/e

즉, 어떻게 SQL 서버 나에게 내 XML의 구조를 말할 수 있습니까?

나는 개별 요소의 모든 이름을 얻기 위해 다음을 수행 할 수 있습니다 :

SELECT  C1.query('fn:local-name(.)')
FROM    my_table
CROSS APPLY my_xml_column.nodes('//*') AS T ( C1 )

트릭을 할 것입니다 요소의 전체 경로를 반환 "지역 이름 ()"에 해당하는이 있었다 아마도 경우?

해결법

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

   1.당신은 XQuery와 재귀 CTE (NO OPENXML)이 깨끗하게 할 수 있습니다 :

   당신은 XQuery와 재귀 CTE (NO OPENXML)이 깨끗하게 할 수 있습니다 :

   DECLARE @xml xml
   SET @xml = '<a><b /><c><d /><d /><d /></c></a>';
   
   WITH Xml_CTE AS
   (
       SELECT
           CAST('/' + node.value('fn:local-name(.)',
               'varchar(100)') AS varchar(100)) AS name,
           node.query('*') AS children
       FROM @xml.nodes('/*') AS roots(node)
   
       UNION ALL
   
       SELECT
           CAST(x.name + '/' + 
               node.value('fn:local-name(.)', 'varchar(100)') AS varchar(100)),
           node.query('*') AS children
       FROM Xml_CTE x
       CROSS APPLY x.children.nodes('*') AS child(node)
   )
   SELECT DISTINCT name
   FROM Xml_CTE
   OPTION (MAXRECURSION 1000)
   

   정말 많은 XQuery에 마법을하고 있지,하지만 적어도 그것은 등 모든 저장 프로 시저, 특별한 권한을 모든 인라인 필요로하지 않는 것

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

   2.당신을 위해 UDF .....

   당신을 위해 UDF .....

   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE FUNCTION [dbo].[XMLTable](@x XML)  
   RETURNS TABLE 
   AS RETURN 
   WITH cte AS (  
   SELECT 
           1 AS lvl,  
           x.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
           CAST(NULL AS NVARCHAR(MAX)) AS ParentName, 
           CAST(1 AS INT) AS ParentPosition, 
           CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
           x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,  
           x.value('local-name(.)','NVARCHAR(MAX)')  
           + N'[' 
           + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)  
           + N']' AS XPath,  
           ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position, 
           x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,  
           x.value('text()[1]','NVARCHAR(MAX)') AS Value,  
           x.query('.') AS this,         
           x.query('*') AS t,  
           CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,  
           CAST(1 AS INT) AS ID  
   FROM @x.nodes('/*') a(x)  
   UNION ALL 
   SELECT 
           p.lvl + 1 AS lvl,  
           c.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
           CAST(p.Name AS NVARCHAR(MAX)) AS ParentName, 
       CAST(p.Position AS INT) AS ParentPosition, 
           CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
           CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath,  
           CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')+ N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') 
           ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,  
           ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
           ORDER BY (SELECT 1)) AS Position, 
           CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,  
           CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, c.query('.') AS this,  
           c.query('*') AS t,  
           CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,  
           CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)  
   FROM cte p  
   CROSS APPLY p.t.nodes('*') b(c)), cte2 AS (  
                                               SELECT 
                                               lvl AS Depth,  
                                               Name AS NodeName,  
                                               ParentName, 
                                               ParentPosition, 
                                               NodeType,  
                                               FullPath,  
                                               XPath,  
                                               Position, 
                                               Tree AS TreeView,  
                                               Value,  
                                               this AS XMLData,  
                                               Sort, ID  
                                               FROM cte  
   UNION ALL 
   SELECT 
           p.lvl,  
           x.value('local-name(.)','NVARCHAR(MAX)'),  
           p.Name, 
           p.Position, 
           CAST(N'Attribute' AS NVARCHAR(20)),  
           p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
           p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
           1, 
           SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)  
           + N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
           x.value('.','NVARCHAR(MAX)'),  
           NULL,  
           p.Sort,  
           p.ID + 1  
   FROM cte p  
   CROSS APPLY this.nodes('/*/@*') a(x)  
   )  
   SELECT 
           ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,  
           ParentName, ParentPosition,Depth, NodeName, Position,   
           NodeType, FullPath, XPath, TreeView, Value, XMLData 
   FROM cte2
   

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

   3.나는 SQL 서버의 XQuery를 구현이 작업까지 아니라고 생각하지만, 이것은 (필요에 따라 적응이 영감)을 수행하는 또 다른 방법이다 :

   나는 SQL 서버의 XQuery를 구현이 작업까지 아니라고 생각하지만, 이것은 (필요에 따라 적응이 영감)을 수행하는 또 다른 방법이다 :

   DECLARE @idoc INT, @xml XML
   SET @xml = (SELECT TOP 1 my_xml_column FROM my_table)
   EXEC sp_xml_preparedocument @idoc OUTPUT, @xml;
   
   WITH
       E AS (SELECT * FROM OPENXML(@idoc,'/',3)),
       P AS
       (
       -- anchor member
       SELECT id, parentid, localname AS [Path]
       FROM E WHERE parentid IS NULL
       UNION ALL
       -- recursive member
       SELECT E.id, E.parentid, P.[Path] + '/' + localname AS [Path]
       FROM P INNER JOIN E ON E.parentid = P.id
       )
   SELECT [Path] FROM P
   
   EXEC sp_xml_removedocument @idoc
   

from https://stackoverflow.com/questions/2266132/how-can-i-get-a-list-of-element-names-from-an-xml-value-in-sql-server by cc-by-sa and MIT license