-5

I need to get the values of Step0-to-Step1_Variance,Step1-to-Step2_Variance from the above XML:

enter image description here

I tried with the query in the attached picture, but getting NULL value

Salman A
  • 262,204
  • 82
  • 430
  • 521
Priya R
  • 1
  • 1
  • What "below" query? – Thom A Nov 18 '19 at 18:51
  • Priya, it would be great if you could provide a minimal reproducible sample: (1) DDL and sample data population, i.e. CREATE plus INSERT statements. (2) What you need to do, i.e. logic. (3) Desired output based on sample data. (4) Your SQL Server version (SELECT @@version;) – Yitzhak Khabinsky Nov 18 '19 at 19:11
  • 1
    Welcome to Stack Overflow. Please take the [tour](https://stackoverflow.com/tour) and also read [How to Ask](https://stackoverflow.com/help/how-to-ask). Stack Overflow is not a free script writing service. Your own research and code attempts are expected. Edit the question to include your code in a [Minimal, Complete, and Verifiable](https://stackoverflow.com/help/minimal-reproducible-example) example. – Brandon McClure Nov 18 '19 at 19:11
  • 2
    No images please, we need T-SQL script. – Yitzhak Khabinsky Nov 18 '19 at 19:20
  • 1
    [Why not upload images of code on SO when asking a question?](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question). Paste your code... An image is all but useless to us. Posting an image is only going to attract more downvotes to your question, not answers. – Thom A Nov 18 '19 at 19:21

2 Answers2

1

I suppose in your actual xml there is one TMAs element with zero or more TMA children. You could select them like so:

SELECT tma.value('(./ActualMin_Step0-to-Step1/@Step0-to-Step1_Variance)[1]', 'INT')
FROM ...
CROSS APPLY tbl.xml.nodes('/myFields/Page3/TMAs/TMA') AS x(tma)
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • I'm a new member here and I'm unable to post my question with my exact code. It gives me error like code indentation is not proper and so on. Thats why pasted picture. Sorry for that. Thank you very much, Salman. This solved my issue. – Priya R Nov 18 '19 at 19:32
  • @PriyaR, Find details about [markdown format here](https://stackoverflow.com/editing-help). – Shnugo Nov 19 '19 at 08:33
0

there is no query, but I imagine you have an XML as below :

<Root>  
<row id="1"><name>Larry</name><oflw>some text</oflw></row>  
<row id="2"><name>moe</name></row>  
<row id="3" />  
</Root>

and you need to get name tag.

DECLARE @x xml   
SET @x='<Root>  
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>  
    <row id="2"><name>moe</name></row>  
    <row id="3" />  
</Root>'  
SELECT T.c.query('.') AS result  
FROM   @x.nodes('/Root/row') T(c)
  • Hi, somehow Im unable to write the code here. yes, my XML is similar but the column names are different and so on. In this case, I have to join all these with the root element? – Priya R Nov 18 '19 at 19:08
  • So you probably can use the example and just change the property names –  Nov 18 '19 at 19:09
  • If you can't show us the code, then we can't help you @PriyaR . Code is just `text`, so just paste it into your question. – Thom A Nov 18 '19 at 19:09
  • SELECT t.x.value('(./ActualMin_Step0_to_Step1/@Step0-to-Step1_Variance)[1]','varchar(10)') as [Step0-to-Step1_Variance] FROM @xml_table tbl CROSS APPLY tbl.xml_data.nodes('/myFields/Page3') e(x) OUTER APPLY e.x.nodes('TMA') t(x) – Priya R Nov 18 '19 at 19:11
  • This is my XML 1 11 – Priya R Nov 18 '19 at 19:11
  • `` does not match ``. – Salman A Nov 18 '19 at 19:22
  • Comments are not the place for sample data either, @PriyaR . Please [**edit**](https://stackoverflow.com/posts/58921047/edit) your question. – Thom A Nov 18 '19 at 19:32