Hi ,

I have a big xml in which there are many elements, which has internal xpath reference to another element.

Here is xml -

<publishers>
  <app.test.module.library.Publisher>
    <typeId>
      <selectedId>101</selectedId>
      <selectedText>Book</selectedText>
    </typeId>
    <regionCode>
      <selectedId>102</selectedId>
      <selectedText>NWD</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
  <app.test.module.library.Publisher>
    <typeId reference="../../app.test.module.library.Publisher/typeId" />
    <regionCode>
      <selectedId>101</selectedId>
      <selectedText>UWY</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
  <app.test.module.library.Publisher>
    <typeId>
      <selectedId>100</selectedId>
      <selectedText>Deposit</selectedText>
    </typeId>
    <regionCode>
      <selectedId>100</selectedId>
      <selectedText>WHU</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
</publishers>

So the typeId of second Publisher element points to the typeId of First Publisher element [ <typeId reference="../../app.test.module.library.Publisher/typeId" /> ]

While querying in the sql, there has to be some way to reach to the referred element but I have not been able to figure out so far.

would appreciate some help here.

Thanks

• Edited by jsh2015 Friday, March 27, 2015 12:27 AM corrected the question with providing more details
• Merged by Eric__ZhangMicrosoft contingent staff, Moderator 21 hours 52 minutes ago the same requirement from one OP

Hi ,

I have a big xml in which there are many elements, which has internal xpath reference to another element.

Here is xml -

<publishers>
  <app.test.module.library.Publisher>
    <typeId>
      <selectedId>101</selectedId>
      <selectedText>Book</selectedText>
    </typeId>
    <regionCode>
      <selectedId>102</selectedId>
      <selectedText>NWD</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
  <app.test.module.library.Publisher>
    <typeId reference="../../app.test.module.library.Publisher/typeId" />
    <regionCode>
      <selectedId>101</selectedId>
      <selectedText>UWY</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
  <app.test.module.library.Publisher>
    <typeId>
      <selectedId>100</selectedId>
      <selectedText>Deposit</selectedText>
    </typeId>
    <regionCode>
      <selectedId>100</selectedId>
      <selectedText>WHU</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
</publishers>

So the typeId of second Publisher element points to the typeId of First Publisher element [ <typeId reference="../../app.test.module.library.Publisher/typeId" /> ]

While querying in the sql, there has to be some way to reach to the referred element but I have not been able to figure out so far.

would appreciate some help here.

Thanks

• Edited by jsh2015 Friday, March 27, 2015 12:27 AM corrected the question with providing more details
• Merged by Eric__ZhangMicrosoft contingent staff, Moderator Monday, March 30, 2015 9:13 AM the same requirement from one OP

Hi,

Is it possible to sort of pre-process xml before querying such as we replace all internal xpath references with the referred elements?

So below is the xml and as we see the typeId element of second app.test.module.library.Publisher element refers to the typeId element in the first app.test.module.library.Publisher element through xpath reference . Seems this is done by XStream(which we use for xml-java) to reduce the size of resulting xml. This is all fine and in java one can easily traverse through such xpath references, but the challenge i am facing is in sql where i use node() function to get each of app.test.module.library.Publisher element and fetch value of selectedText element of both the children of app.test.module.library.Publisher - (typeId, regionCode). 

-------------------------------

declare @publishers xml
set @publishers = '
<publishers>
  <app.test.module.library.Publisher>
    <typeId>
      <selectedId>101</selectedId>
      <selectedText>Book</selectedText>
    </typeId>
    <regionCode>
      <selectedId>102</selectedId>
      <selectedText>NWD</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
  <app.test.module.library.Publisher>
    <typeId reference="../../app.test.module.library.Publisher/typeId" />
    <regionCode>
      <selectedId>101</selectedId>
      <selectedText>UWY</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
  <app.test.module.library.Publisher>
    <typeId>
      <selectedId>100</selectedId>
      <selectedText>Deposit</selectedText>
    </typeId>
    <regionCode>
      <selectedId>100</selectedId>
      <selectedText>WHU</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
</publishers>';

select typeId = el.e.value('(typeId/selectedText)[1]','varchar(max)')
      ,regionCode = el.e.value('(regionCode/selectedText)[1]','varchar(max)')
from @publishers.nodes('/publishers/app.test.module.library.Publisher') as el(e)

-------------------------------

If you execute this, the second typeId value will be null but ideally I should be able to reach to the reference, which is typeId of first app.test.module.library.Publisher element and should be able to fetch selectedText of it, which is correct value (Book). In other words first two app.test.module.library.Publisher elements are of same type (Book).

Since I am not able to find the way to do it, I thought I should have some way to process this xml to replace all such references with the referred element and that will eliminate the need to go for tedious xpath reference based access.

Thanks

• Edited by jsh2015 Friday, March 27, 2015 6:07 PM more details provided

Any chance you could post an complete example?

Hi, I ve updated the question providing more details.

You need to search for an attribute (not element).  Any property inside a tag with an equal sign is call an attribute.  See webpage below

http://stackoverflow.com/questions/25135788/linq-to-xml-query-get-element-attribute

• Edited by Joel Engineer Friday, March 27, 2015 12:45 AM

Actually I need to go to that referenced element and as I have mentioned in the question, this needs to be achieved in a sql query not in java.

What Java are you refereeing to?

simply put, if you can tell me how can you get typeId/selectedText value for the second app.test.module.library.Publisher element through a sql query ?

Regarding <typeId reference="../../app.test.module.library.Publisher/typeId" />

Literally your are attempting to reference the "../../app.test.module.library.Publisher/typeId" for currect typeId. I have limit knowledge in XML, may you tell where did you find the "reference" keyword in XML that can make your attempt come true or you invent it yourself?

The "reference="../../app.test.module.library.Publisher/typeId" " will be parsed to an attribute as literal eventually I believe.

I see your another post here, and you may be asking for the same thing.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ca116c5d-b3c8-47cb-aae0-d76d4f55147d/querying-xml-element-which-refers-to-another-element-through-xpath-reference?forum=sqlxml 

• Edited by HoroChan Sunday, March 29, 2015 2:11 AM

I think I have provided all the details. I have mentioned that when you use some xml processing libs like XStream, they probably do this (replacing repeating elements with such xpath references) to make the size smaller and which is very valid approach. And so i thought there must be an easy way in MS SQL to query such element, which basically has a reference (reference attribute) to an internal element.

The another post you are referring to, that as well if you carefully read its not the same question. The two questions together are more like  if-then-else scenario.

faced with this kind of xml with internal references, my question is what is the way to query such elements. IF its hard to query i thought if i can find way an easy way to pre-process the xml, replacing all such references with the referred elements then we will not have to worry about references at all..

I am afraid that is a specific feature of XStream lib, as far as I know, there's not such a built-in function in SQL Server. The only approach can be a CLR funtion referencing the XStream lib for C#.

I am afraid that is a specific feature of XStream lib, as far as I know, there's not such a built-in function in SQL Server. The only approach can be a CLR funtion referencing the XStream lib for C#.

• Proposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator 17 hours 52 minutes ago
• Marked as answer by Eric__ZhangMicrosoft contingent staff, Moderator 17 hours 33 minutes ago

I was able to get a result using dynamic SQL, but my guess is if you have more complicated examples it probably won't work.  I also make an assumption you know the full path of the first reference you want.  Anyway, try this:

DECLARE @xml XML = '<publishers>
  <app.test.module.library.Publisher>
    <typeId>
      <selectedId>101</selectedId>
      <selectedText>Book</selectedText>
    </typeId>
    <regionCode>
      <selectedId>102</selectedId>
      <selectedText>NWD</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
  <app.test.module.library.Publisher>
  <!--<typeId reference="../../app.test.module.library.Publisher/typeId" />-->
    <typeId reference="../../app.test.module.library.Publisher/typeId" />
    <regionCode>
      <selectedId>101</selectedId>
      <selectedText>UWY</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
  <app.test.module.library.Publisher>
    <typeId>
      <selectedId>100</selectedId>
      <selectedText>Deposit</selectedText>
    </typeId>
    <regionCode>
      <selectedId>100</selectedId>
      <selectedText>WHU</selectedText>
    </regionCode>
  </app.test.module.library.Publisher>
</publishers>'


-- So you basically need to create and execute this string;
-- but how do you work out the first bit of the string ?
--SELECT @xml.query('(publishers/app.test.module.library.Publisher/typeId/../../app.test.module.library.Publisher/typeId)[1]');

------------------------------------------------------------------------------------------------------------------------------------
DECLARE @fullPath NVARCHAR(MAX) = 'publishers/app.test.module.library.Publisher/typeId/@reference'	-- the full path of the reference
DECLARE @shortPath NVARCHAR(MAX) = LEFT( @fullPath, CHARINDEX( '@', @fullPath ) - 1 )	-- full path without attribute name

IF @shortPath IS NULL
	RAISERROR ( 'Unable to get @shortPath.', 16, 1 )


-- Start processing the XML
DECLARE @sql NVARCHAR(MAX), @reference NVARCHAR(MAX)

-- Extract the reference
SET @reference = @xml.value('(publishers/app.test.module.library.Publisher/typeId/@reference)[1]', 'NVARCHAR(MAX)')

-- Construct the sql
SET @sql = 'SELECT @xml.query(''(' + @shortPath + @reference + ')[1]'')'

-- Execute the SQL
EXEC sp_executesql @sql, N'@sql NVARCHAR(MAX), @xml XML', @sql, @xml

Post back any issues you have with that approach and your real data.