Extracting Information from an XML field

Question

question

ethanBRAC-1225 asked Apr 25, '22 ErlandSommarskog commented Apr 25, '22

Extracting Information from an XML field

Hello, Recently we have received a lot of data in XML format and we would like to store the XML data the way they are in an ODBC database. We are thinking perhaps it will be easier if we create a table that has all the XML data as a data field in there and then later we just query this table to get the info we need using xpath. Unfortunately the data format we receive is very complicated, i.e. the IDs instead of being an attribute of the elements that could be used to search for unique elements are used to link the XML elements internally to another XML element and appear as subelements. For example, the ID shown in the example XML snippet below is used to tag this element so that it can be referred to somewhere else in the XML file, or the rSubmatrix (344) is the ID of an iSubmatrix that is referred to here. Moreover, the IDs change every time we generate the XML file. The question that I have is, is it possible to extract the value of s element when XML element are not in the same level using a SQL query in such a structure in one go? basically all we have is the iName - since the very many LocalColumn elements are placed in a nested structure we don't have the address and unfortunately each is having a different ID. Thank you

<other elements...>
<Nested elemnets>
<LocalColumn>
<Id>14945</Id>
<iName>rep_regression_torque_no_regression#</iName>
<iGlobal_flag>15</iGlobal_flag>
<iIndependent>0</iIndependent>
<iSequence_representation>explicit</iSequence_representation>
<iValues>
<A_UTF8STRING>
<s>16.89</s>
</A_UTF8STRING>
</iValues>
<rMeasurementQuantity>11069</rMeasurementQuantity>
<rSubmatrix>344</rSubmatrix>
</LocalColumn>
<Nested elemnets>
<other elements..>

sql-server-general sql-server-transact-sql azure-sqldatabase-edge

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-04-25T16:19:01.75Z

Viorel-1 answered Apr 25, '22

Check an example:

 declare @xml varchar(max) = '
 <Data>
 <OtherElements>
 <LocalColumn>
 <Id>9999</Id>
 <iName>another name</iName>
 <iGlobal_flag>15</iGlobal_flag>
 <iIndependent>0</iIndependent>
 <iSequence_representation>explicit</iSequence_representation>
 <iValues>
 <A_UTF8STRING>
 <s>900</s>
 </A_UTF8STRING>
 </iValues>
 <rMeasurementQuantity>11069</rMeasurementQuantity>
 <rSubmatrix>344</rSubmatrix>
 </LocalColumn>
 <LocalColumn>
 <Id>14945</Id>
 <iName>rep_regression_torque_no_regression#</iName>
 <iGlobal_flag>15</iGlobal_flag>
 <iIndependent>0</iIndependent>
 <iSequence_representation>explicit</iSequence_representation>
 <iValues>
 <A_UTF8STRING>
 <s>16.89</s>
 </A_UTF8STRING>
 </iValues>
 <rMeasurementQuantity>11069</rMeasurementQuantity>
 <rSubmatrix>344</rSubmatrix>
 </LocalColumn>
 </OtherElements>
 </Data>
 '
    
 declare @iName varchar(max) = 'rep_regression_torque_no_regression#'
    
 declare @s varchar(max)
    
 set @s = cast(@xml as xml).value('(//LocalColumn[iName=sql:variable("@iName")]/iValues/A_UTF8STRING/s)[1]', 'varchar(max)')
    
 print @s -- result: 16.89

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-04-25T16:35:53.897Z

ethanBRAC-1225 answered Apr 25, '22 ErlandSommarskog commented Apr 25, '22

Thank you. I didn't expect to get an answer so fast. :) I don't have access to a server at the moment; I will try your approach and will let you know how it goes.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog · Apr 25 at 09:43 PM

Don't forget to accept Viorel's answer once you have validated it!

0 ·

question