How to Get Node Name and Values from an Xml Variable in T-Sql

How to get node name and values from an xml variable in t-sql

You can use the local-name() function to the get name of the XML node - try something like this:

DECLARE @input XML =  '...your xml here.....'

SELECT
    NodeName = C.value('local-name(.)', 'varchar(50)'),
    NodeValue = C.value('(.)[1]', 'varchar(50)') 
FROM @input.nodes('/Surveys/Svy/*') AS T(C)

This should give you an output something like:

Get value by passing dynamic node name of XML

Are you looking for something along this?

DECLARE @xml AS XML = 
'<AuditMsg>
  <SourceDb>TestDatabase</SourceDb>
  <SourceTable>Person</SourceTable>
  <PKFieldName>ID</PKFieldName>
  <UserId>sa</UserId>
  <DMLType>I</DMLType>
  <OldData />
  <ChangedData>
    <t xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <ID>4</ID>
      <FirstName>name 4</FirstName>
      <LastName>surname 4</LastName>
      <DateOfBirth>444444</DateOfBirth>
    </t>
  </ChangedData>
</AuditMsg>'

--your externally defined variable

DECLARE @Attribute varchar(100) = 'ID'

--The query

SELECT @xml.value('(/AuditMsg
                    /ChangedData
                    /t
                    /*[local-name()=sql:variable("@Attribute")]
                    /text())[1]','nvarchar(max)');

The XPath will dive down into <t> and find the element, where the element's name equals your parameter passed in via sql:variable(). Within this element we pick the text() node.

Select nodes in T-SQL XML by name given in a variable

Your XPath expression misses a * around /data/ and has some superfluous [] around sql:variable("@node"):

it should be

select
  ref.value('id[1]','bigint') as Id,
  ref.value('weight[1]','bigint') as Weight
from @xdoc.nodes('/data/*[local-name()=sql:variable("@node")]') as xdata(ref)

EDIT: added correction mentioned in comment by author.

SQL get values from xml with same node name

Try using an outer apply first and then binding the two in the where clause.

SELECT tag.name.value('.','nvarchar(20)')
FROM @tab tab
    outer apply tab.xmldata.nodes('/Values/Required/Name') tag(name)
where tab.Usernames = tag.name.value('.','nvarchar(20)')

Also, if it doesn't already exist, consider an XML Index.

SQL Variables in XML Node

As Jeroen Mostert told you in a comment, this is not possible. You can use local-name() in connection with sql:variable() to test for a specific element, but you cannot introduce a XPath-variable.

You can build the whole statement dynamically and use EXEC() or sp_executesql() for the execution, or you can use a recursive CTE to walk down the XML and return each element with it's path.

DECLARE @x TABLE(item XML)
DECLARE  @schemaname VARCHAR(100)
SET @schemaname = 'Bla'

INSERT into @x
SELECT  
'<GaleriesSchem2>
    <Hello>
        <Bla>
            <Image_1 OriginalName="Image">12.jpg</Image_1>
            <Image_2 OriginalName="Image2">45.jpg</Image_2>
        </Bla>
    </Hello>
</GaleriesSchem2>
<GaleriesSchem3>
  <Image_1 OriginalName="Image">67.jpg</Image_1>
  <Image_2 OriginalName="Image2">89.jpg</Image_2>
</GaleriesSchem3>';

WITH recCTE AS
(
    SELECT CONCAT('/',CAST(TopLevelNode.value('local-name(.)','nvarchar(max)') AS NVARCHAR(MAX)),'/') AS NodePath
          ,TopLevelNode.query('./*') AS SubNodes
          ,TopLevelNode.value('text()[1]','nvarchar(max)') AS Content
    FROM @x 
    CROSS APPLY item.nodes('*') A(TopLevelNode)

    UNION ALL

    SELECT CONCAT(r.NodePath,CAST(TheNextLevel.value('local-name(.)','nvarchar(max)') AS NVARCHAR(MAX)),'/')
          ,TheNextLevel.query('./*')
          ,TheNextLevel.value('text()[1]','nvarchar(max)') AS Content
    FROM recCTE r
    OUTER APPLY r.SubNodes.nodes('*') A(TheNextLevel)
    WHERE r.SubNodes.exist('*')=1
)
SELECT *
FROM recCTE 
WHERE Content IS NOT NULL;

The result

NodePath                            Content
/GaleriesSchem3/Image_1/            67.jpg
/GaleriesSchem3/Image_2/            89.jpg
/GaleriesSchem2/Hello/Bla/Image_1/  12.jpg
/GaleriesSchem2/Hello/Bla/Image_2/  45.jpg

Against this set you might use your XPath in a simple LIKE...

But - to be honest - this is very limited and rather slow...

SQL - Read XML node value from attribute name

Here you have something that should help:

declare @xml xml

set @xml = '<metadata>
   <meta id="TypeX" valuetype="xs:string">
      <values>
         <value>3</value>
      </values>
   </meta>
   <meta id="TypeY" valuetype="xs:string">
      <values>
         <value>5</value>
      </values>
   </meta>
</metadata>'

select C.value('(./@id)', 'varchar(5)') AS [Meta ID]
     , C.value('(./values/value)[1]', 'tinyint') AS [Value]
from @xml.nodes('/metadata/meta') AS T(C)

Moreover, it could be useful for you to get familiar with XQueries :)

You can change data type in select statement from varchar(5) to some bigger varchar or from tinyint to some "bigger" type, if you need to.

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.

---

Related Topics