Share via

Get First Node of XML In SQL Query

  • Article
  • 01/21/2011

Question

Friday, January 21, 2011 4:58 PM

I need to know if it's possible to find the first node of an XML type with a generic query.  For example,

Given:

DECLARE @x XML

SET @x =

'<Root>

<Node1>

<OptionalNode(s) />

</Node1>

</Root>'

I'd like to be able to retrieve the Node Name and Attributes of 'Node1' without knowing the path.  Potentially, the actual value of Node1 could be any number of things and I'd prefer not to have to write a query for each potential path.  Is there a way to have a dynamic path in an XML query?

 

All replies (6)

Friday, January 21, 2011 6:13 PM ✅Answered | 1 vote

Here are some examples to get you started:

DECLARE @x XML
SET @x =
'<Root>
 <Node1 id="1" name="test1">
  <OptionalNodes />
 </Node1>
</Root>'

-- Get the second node name
DECLARE @elementName   VARCHAR(50)

SELECT @elementName = a.c.value('local-name(.)', 'VARCHAR(50)')
FROM @x.nodes('*/*') a(c)

SELECT @elementName elementName
 
 -- Get second element name and it's attributes
SELECT 
    a.c.value('local-name(.)', 'VARCHAR(50)') AS secondNode,
    b.c.value('local-name(.)', 'VARCHAR(50)') AS attributes
FROM @x.nodes('*/*') a(c)
    CROSS APPLY a.c.nodes('./@*') b(c)

-- Use the element name you fetched to query the SQL dynamically using sql:variable
SELECT 
    a.c.query('.')
FROM @x.nodes('//*[local-name()=sql:variable("@elementName")]') a(c)

Friday, January 21, 2011 5:58 PM

I am not following well your question, since the name of the node is Node1 and there is no attribute for that element. Can you expand the example, may be posting a couple of them, and what is the expected result?

See if this helps.

DECLARE @x XML;

SET @x = '<root><Node1 a="1" b="2" /></root>';

SELECT
    N.x.value('local-name(.)', 'varchar(50)') AS attr_name,
    N.x.value('data(.)', 'varchar(50)') AS attr_value
FROM
    @x.nodes('root//Node1[1]/@*') AS N(x);

SET @x = '<root><whatever><Node1 w="3" />whatever <Node1 a="1" b="2" /></whatever></root>';

SELECT
    N.x.value('local-name(.)', 'varchar(50)') AS attr_name,
    N.x.value('data(.)', 'varchar(50)') AS attr_value
FROM
    @x.nodes('root//Node1[1]/@*') AS N(x);
GO

 

AMB

Some guidelines for posting questions...

Friday, January 21, 2011 7:07 PM

Cool stuff, as always, wBob... I was hoping you'd chime in on this one.

 

--Brad (My Blog)

Friday, January 21, 2011 7:26 PM

To expand my previous post, and use a variable for the element name, then:

USE tempdb;
GO
DECLARE @x XML;
DECLARE @element_name varchar(50);

SELECT
 @x = '<root><Node1 a="1" b="2" /></root>',
 @element_name = 'Node1';

SELECT
    N.x.value('local-name(.)', 'varchar(50)') AS attr_name,
    N.x.value('data(.)', 'varchar(50)') AS attr_value
FROM
    @x.nodes('(root//*[local-name(.)=sql:variable("@element_name")][1]/@*)') AS N(x);

SET @x = '<root><whatever><Node1 w="6" /> whatever <Node1 a="1" b="2" /></whatever></root>';

SELECT
    N.x.value('local-name(.)', 'varchar(50)') AS attr_name,
    N.x.value('data(.)', 'varchar(50)') AS attr_value
FROM
    @x.nodes('(root//*[local-name(.)=sql:variable("@element_name")][1]/@*)') AS N(x);
GO

Notice:

1 - The name of the element is in the variable.
2 - The element could be at any level, it doesn't have to be directly after the root element, and it will return just the first occurence.

 

AMB

Some guidelines for posting questions...

Friday, January 21, 2011 9:17 PM

Here are some examples to get you started:

DECLARE @x XML

SET @x =

'<Root>

 <Node1 id="1" name="test1">

 <OptionalNodes />

 </Node1>

</Root>'



-- Get the second node name

DECLARE @elementName    VARCHAR(50)



SELECT @elementName = a.c.value('local-name(.)', 'VARCHAR(50)')

FROM @x.nodes('*/*') a(c)



SELECT @elementName elementName

 

 -- Get second element name and it's attributes

SELECT 

    a.c.value('local-name(.)', 'VARCHAR(50)') AS secondNode,

    b.c.value('local-name(.)', 'VARCHAR(50)') AS attributes

FROM @x.nodes('*/*') a(c)

    CROSS APPLY a.c.nodes('./@*') b(c)



-- Use the element name you fetched to query the SQL dynamically using sql:variable

SELECT 

    a.c.query('.')

FROM @x.nodes('//*[local-name()=sql:variable("@elementName")]') a(c)

This, I think, is what I was after.  I'm sorry my initial post wasn't all that clear: I'm not sure I really knew how to ask the question, so thanks for extrapolating what I might need from that.

I'm not a complete SQL novice, but it's definitely not my strongest point.

I have a project I'm working on where I'll be passing data to/from the DB in XML which I then want SQL Server to disassemble and use for various operations.  But I don't want to necessarily be stuck to one particular format.  For instance, I'd like to construct my SPROC in such a way that:

<Root> <Person id="123" Name="Abc" > <Address PersonID="123" Street="123 Any St" City="Anywhere" State="CO" Etc="Etc" /></Person></Root>

and

<Root> <Organization id="123" Name="Some Org" > <Address PersonID="123" Street="785 Any St" City="Anywhere" State="CO" Etc="Etc" /></Organization ></Root>

are the same to the procedure, but I can do different things based on which node is the first one in the list (ie branch via a SELECT CASE based on which node).

I think, from initial examination, that you've solved the problem here.  Thank you!

Friday, January 21, 2011 11:52 PM

My suggestion is to pass what you know to the xquery engine, and not try to create one proc to fit all.

For example, if your doc will always have <Root> element and you will be accessing the first element always, then it will be better to tell the xquery engine that you are just interested in (Root/*)[1].

Check these two queries, and compare execution plans and times.

DECLARE @x XML

SET @x = '<Root><Organization ID="1234" Name="Some Org"><Address OrganizationID="123" Street="785 Any St" City="Anywhere" State="CO" Etc="Etc" /></Organization></Root>';

SELECT
 N.x.value('local-name(.)', 'varchar(50)') AS elem_name,
 A.x.value('local-name(.)', 'varchar(50)') AS attr_name,
 A.x.value('data(.)', 'varchar(50)') AS attr_value
FROM
 @x.nodes('(Root/*)[1]') AS N(x)
 CROSS APPLY
 N.x.nodes('@*') AS A(x);

SELECT
 N.x.value('local-name(.)', 'varchar(50)') AS elem_name,
 A.x.value('local-name(.)', 'varchar(50)') AS attr_name,
 A.x.value('data(.)', 'varchar(50)') AS attr_value
FROM
 @x.nodes('*/*') AS N(x)
 CROSS APPLY
 N.x.nodes('@*') AS A(x);
GO

If the attributes of the first element will be the same (ID, Name), then you can get those directly and use the proper type instead returning everything as varchar. Again, check the execution plans.

DECLARE @x XML

SET @x = '<Root><Organization ID="1234" Name="Some Org"><Address OrganizationID="123" Street="785 Any St" City="Anywhere" State="CO" Etc="Etc" /></Organization></Root>';

SELECT
 @x.value('local-name((Root/*)[1])', 'varchar(50)') AS elem_name,
 @x.value('(Root/*)[1]/@ID[1]', 'int') AS ID,
 @x.value('(Root/*)[1]/@Name[1]', 'varchar(50)') AS Name
 
SELECT
 E.x.value('local-name(.)', 'varchar(50)') AS elem_name,
 E.x.value('@ID[1]', 'int') AS ID,
 E.x.value('@Name[1]', 'varchar(50)') AS Name
FROM
 @x.nodes('(Root/*)[1]') AS E(x)

SELECT
 E.x.value('local-name(.)', 'varchar(50)') AS elem_name,
 A.x.value('local-name(.)', 'varchar(50)') AS attr_name,
 A.x.value('data(.)', 'varchar(50)') AS attr_value
FROM
 @x.nodes('(Root/*)[1]') AS E(x)
 CROSS APPLY
 E.x.nodes('@*[position() < 3]') AS A(x);
GO

 

AMB

Some guidelines for posting questions...