How to Get a List of Element Names from an Xml Value in SQL Server

How can I get a list of element names from an XML value in SQL Server

You can do this cleanly with XQuery and a recursive 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)

It's not really doing much XQuery magic, but at least it's all inline, doesn't require any stored procedures, special permissions, etc.

Extracting element names from XML in SQL Server 2012

Something like this?

DECLARE @input XML = '<root>
     <e1>
       <l2>
         <data1>test</data1>
       </l2>
       <l3>
         <data3>test3</data3>
       </l3>
     </e1>
     <e2>
        <data1>test</data1>
        <data2>test2</data2>
     </e2>
    </root>'

To get the immediate descendant nodes under <root>, you can use this query:

SELECT
    XC.value('local-name(.)', 'varchar(20)')
FROM 
    @input.nodes('/root/*') AS XT(XC)

Returns:

e1
e2

And this query to get the descendants of <e2>:

SELECT
    XC.value('local-name(.)', 'varchar(20)')
FROM 
    @input.nodes('/root/e2/*') AS XT(XC)

returns:

data1
data2

Get unique list of top level element names in XML column of SQL table

 select  XmlColumn.value('local-name(/*[1])','varchar(100)')
 from MyTable

Select all XML nodes from XML column

The following will transform your flat XML without a root node into a classical EAV list:

DECLARE @tbl TABLE(ID INT IDENTITY, YourXml XML);
INSERT INTO @tbl VALUES
 ('<tag1>1</tag1><tag2>2</tag2>')
,('<tag3>3</tag3><tag4>4</tag4><tag5>5</tag5>');

--The query

SELECT t.ID
      ,AllNodes.value('local-name(.)','nvarchar(max)') AS TagName
      ,AllNodes.value('text()[1]','nvarchar(max)') AS TagValue
FROM @tbl t
CROSS APPLY t.YourXml.nodes('/*') A(AllNodes);

The XQuery function local-name() allows to query for meta data, the text() node represents the element's content.

Retrieving XML element name using t-SQL

Actually, sorry, the best I've got is:

select distinct r.value('fn:local-name(.)', 'nvarchar(50)') as t
FROM
    @xml.nodes('//quotes/*/*') AS records(r)

Guess I answered my own question...

Get list of elements and their values from an untyped xml fragment in T-SQL

declare @xml xml
set @xml = '<Root><A>a1</A><B>b1</B><C>c1</C><D><E>e1</E></D><F>f1<G>g1</G></F></Root>'

select
    element.value('fn:local-name(.)', 'varchar(max)') as Element,
    element.value('text()[1]', 'varchar(max)') as Value
from @xml.nodes('/*//*') as nodes(element)

OUTPUT:

Element  Value
-------- ----------
A        a1
B        b1
C        c1
D        NULL
E        e1
F        f1
G        g1

(7 row(s) affected)

How to get list of XML attributes in a XML node

Try this,

DECLARE @XML as xml

SET @XML = '<XML><row ID="1" x1="x1value" x2="x2value" /></XML>'

SELECT 
    k.value('@ID','BIGINT') AS ID,
    b.value('local-name(.)','VARCHAR(50)') AS ColumnName,  -- get attribute name
    b.value('.','VARCHAR(MAX)') AS Value  -- get attribute value
FROM @XML.nodes('/XML/row') p(k)
CROSS APPLY k.nodes('@*') a(b)

Output:

ID  ColumnName   Value    
--- ------------ -----------
1   ID           1
1   x1           x1value
1   x2           x2value

Hope this helps

Get all values from an XML column

You did not state your expected output clearly, but this seems to be rather easy. XML can deal with generic structures quite well:

DECLARE @xml XML=
'<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>';

--The query will use .nodes() with a path to /*.

--This will return all elements below <Fields>, however they are named

--The query will return the element's name (local-name(.)) together with the two nested elements for old and new value:

SELECT fld.value('local-name(.)','nvarchar(max)') AS FieldName
      ,fld.value('(OldValue/text())[1]','nvarchar(max)') AS OldValue
      ,fld.value('(NewValue/text())[1]','nvarchar(max)') AS NewValue
FROM @xml.nodes('/LogMessage/Fields/*') A(fld);

The result

FieldName           OldValue    NewValue
-----------------------------------------
TransactionCount    NULL        NULL
PersonnelType       Contractor  Employee 
Disabled            TRUE        FALSE 
Expiration          10/31/2018  12/31/2019 

UPDATE

The same against a table's column:

DECLARE @mockup TABLE(ID INT IDENTITY,YourXml XML)
INSERT INTO @mockup VALUES
('<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>');

SELECT fld.value('local-name(.)','nvarchar(max)') AS FieldName
      ,fld.value('(OldValue/text())[1]','nvarchar(max)') AS OldValue
      ,fld.value('(NewValue/text())[1]','nvarchar(max)') AS NewValue
FROM @mockup m
OUTER APPLY m.YourXml.nodes('/LogMessage/Fields/*') A(fld)

How to fetch all value of an element from xml column in SQL

From your question I take, that you want to filter your XML to show only male people. You achieve this with a filter predicate in .nodes().

Attention: The compare string is "(space)Male(space)" because your data is this way.

Create table dbo.sampleXML( ID int identity(1,1) primary key,
Name char(10) not null,
content xml null)
go

insert into dbo.sampleXML (Name,content) values ('ss',CAST('<Employee>
<FirstName> ss </FirstName>
<LastName> nn </LastName>
<Gender> Male </Gender>
<Age>31</Age>
</Employee>' as xml));
insert into dbo.sampleXML (Name,content) values ('xx',CAST('<Employee>
<FirstName> xx </FirstName>
<LastName> yy </LastName>
<Gender> Male </Gender>
<Age>3</Age>
</Employee>' as xml));
insert into dbo.sampleXML (Name,content) values ('dd',CAST('<Employee>
<FirstName> ss </FirstName>
<LastName> dd </LastName>
<Gender> Female </Gender>
<Age>30</Age>
</Employee>' as xml));

SELECT ID
      ,Filtered.Males.value('Gender[1]','varchar(max)') AS Gender 
      ,Filtered.Males.value('FirstName[1]','varchar(max)') AS FirstName 
      ,Filtered.Males.value('LastName[1]','varchar(max)') AS LastName
      ,Filtered.Males.value('Age[1]','int') AS Age
FROM dbo.sampleXML 
CROSS APPLY content.nodes('/Employee[Gender=" Male "]') AS Filtered(Males);

The result

ID  Gender  FirstName   LastName    Age
1    Male    ss           nn        31
2    Male    xx           yy        3

Get the names of attributes from an element in a SQL XML column

DECLARE @xml as xml
DECLARE @path as varchar(max)
DECLARE @index int, @count int

SET @xml = 
'<doc>
 <a>1</a>
 <b ba="1" bb="2" bc="3" />
 <c bd="3"/>
</doc>'

SELECT @index = 1

SET @count = @xml.query('count(/doc/b/@*)').value('.','int')

WHILE @index <= @count 
BEGIN
    SELECT  @xml.value('local-name((/doc/b/@*[sql:variable("@index")])[1])', 'varchar(max)')
    SET @index = @index + 1
END

for element 'b'

it returns

• ba
• bb
• bc

You can build a loop to get attributes for each element in the xml.

BTW
The XML in your sample should be closed at closing doc tag.

---

Related Topics