CODEWITHSUNDEEP
sqlxmlsql-server-2008xpathsql-server-openxml
Anders Nilsson
Anders Nilsson

Reputation: 1485

SQL Server Xquery retrieve generated id for child parent relationship

Is it possible to use Xpath in a query to get child parent relation ship using XQuery. If I use EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<xmlns:ns="http://some_namespace"/>' and after that use the metadata properties I will receive a correct parent - child id relationship i.e

SELECT  *
FROM    OPENXML(@hDoc,'/ns:root/ns:component/ns:indicator')
WITH(
componentID INT '@mp:parentid',
indicatorID INT '@mp:id',
componentIndicatorType VARCHAR(5) '@sometype') componentIndicators

This works but if I were to use XQuery is this without any sp_xml_preparedocument and instead directly access a table storing the XML in a xml column with an index like 

;WITH XMLNAMESPACES (
    'http://some_namespace' AS ns   )
     select
   **** --> Here I would like to retrieve a parent -- child relation id 
   f.i.value(N'@sometype', N'nvarchar(100)') AS sometype
from dbo.xmldatatest AS  T
  cross apply T.xmldata.nodes('ns:root/ns:component/ns:indicator') as f(i)

So is it possible to get such a node relationship when you don't have that explicitly in the xml as attributes using xquery?

Secondly is there a preferred way (OPENXML/XQuery) related to performance and scalability when you have GB+ size xml documents ?

Upvotes: 0

Views: 432

Answers (0)

Related Questions