Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for May, 2014

OpenXML parentID

Posted by Simon Cho on 05/19/2014

I used long time ago.

To find the it again, I spent a lot of time.

This is really helpful to identify XML parentID and currentID.

OpenXML support it.

 

Metaproperty attribute Description
@mp:id Provides system-generated, document-wide identifier of the DOM node (element, attribute, and so on). This ID is guaranteed to refer to the same XML node as long as the document is not reparsed.An XML ID of 0 indicates that the element is a root element. Its parent XML ID is NULL.
@mp:localname Stores the local part of the name of the node. It is used with prefix and namespace URI (Uniform Resource Identifier) to name element or attribute nodes.
@mp:namespaceuri Provides the namespace URI of the current element. If the value of this attribute is NULL, no namespace is present
@mp:prefix Stores the namespace prefix of the current element name.If no prefix is present (NULL) and a URI is given, indicates that the specified namespace is the default namespace. If no URI is given, no namespace is attached.
@mp:prev Stores the previous sibling relative to a node, thereby, providing information about the ordering of elements in the document.@mp:prev contains the XML ID of the previous sibling that has the same parent element. If an element is at the beginning of the sibling list, @mp:prev is NULL.
@mp:xmltext This metaproperty is used for processing purposes. Is the textual serialization of the element and its attributes and subelements as used in the overflow handling of OPENXML.

 

Parent metaproperty attribute Description
@mp:parentid Corresponds to ../@mp:id
@mp:parentlocalname Corresponds to ../@mp:localname
@mp:parentnamespacerui Corresponds to ../@mp:namespaceuri
@mp:parentprefix Corresponds to ../@mp:prefix

 

http://technet.microsoft.com/en-us/library/aa226531%28v=sql.80%29.aspx

http://technet.microsoft.com/en-us/library/ms178088.aspx

 

DECLARE @X XML, @h INT
SET @X = '<root><element>test</element><element>test2</element></root>'

EXEC sp_xml_preparedocument @h OUTPUT, @x

select @h

SELECT * 
  FROM OPENXML (@h, './root/element',8)
  WITH 
  (
  id bigint '@mp:id'
  , parentid bigint '@mp:parentid'
  , element varchar(255) '.'
  )

 EXEC sp_xml_removedocument @h

 

Advertisements

Posted in Common | Tagged: , , | Leave a Comment »