How to get parent element name using OpenXML in sqlserver

Question

If I have some xml:

<Users>
    <User>
        <property1>sdfd</property1>
        ...
    <User>
    ... 
</Users>

And my sql is:

SELECT 
    *
FROM
    OpenXML(@idoc, '/Users/User')
    WITH (  
          [property1] varchar(50) 'property1',
          ...
    )

How can I get the name of the parent element and return that in the dataset?

What you mean by "parent element"? Parent to which one? Please, give some example of the input data and expected output. — Lukasz Lysik
– Lukasz Lysik, Commented Sep 14, 2009 at 17:28
In this case, the parent element would be 'Users 'because everthing is relative to 'User' due to the rowpattern specified in the OpenXML statement — Jeremy
– Jeremy, Commented Sep 14, 2009 at 18:37

marc_s · Accepted Answer · 2009-09-14 21:25:08Z

1

This page from MSDN docs on OpenXML seems to indicate you should be able to use the ".." notation for the parent:

declare @idoc int
declare @doc varchar(1000)

set @doc ='<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" 
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" 
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'

--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc

-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity')

Does that work in your case, too?

UPDATE:
Try this (the "pseudo-attribute" @mp:parentlocalname) :

SELECT *
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity',
               ParentNodeName  varchar(50) '@mp:parentlocalname' )

Does this now do what you want? :-)

See a whole list of these "pseudo-attributes" in this article at ExtremeExperts.

Marc

edited Sep 14, 2009 at 21:25

answered Sep 14, 2009 at 19:05

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeremy Over a year ago

.. will reference the parent element, but doesn't return me the element name. it returns the entire nodeset of the parent including its children.

Jeremy Over a year ago

That did it, I used @mp:parentlocalname and @mp:localname. Thanks, I didn't know about pseudo attributes.

Collectives™ on Stack Overflow

How to get parent element name using OpenXML in sqlserver

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related