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
Simon's SQL 
Leave a Reply