sql-serverxmlt-sqlxquery
T-SQL process custom XML
I need help to process custom XML. I put an excerpt below where I am looking to get all those values marked with blue lines. I'm confused because the attributes for each of the could be different. I also attaching complete running code for testing.
I tried different approaches that I know but the vendor changed the schema and now I can't figure it out.
Thanks so much.
Looking to get all those values also
marked with blue lines:
I just get this running code which doesn't work properly yet,
DECLARE @xml XML = ('<Submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Measures>
<A100 versionID="A01">
<Metadata>
<TY_Year>2020</TY_Year>
<Benefit>true</Benefit>
</Metadata>
<AnnualCount>
<Stratification>
<Pool>11-14</Pool>
</Stratification>
<Data>
<RegTotal>13071</RegTotal>
<CountA>542</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>15-18</Pool>
</Stratification>
<Data>
<RegTotal>12016</RegTotal>
<CountA>458</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>19-20</Pool>
</Stratification>
<Data>
<RegTotal>4591</RegTotal>
<CountA>129</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>2-3</Pool>
</Stratification>
<Data>
<RegTotal>5649</RegTotal>
<CountA>127</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>4-6</Pool>
</Stratification>
<Data>
<RegTotal>8995</RegTotal>
<CountA>366</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>7-10</Pool>
</Stratification>
<Data>
<RegTotal>11818</RegTotal>
<CountA>546</CountA>
</Data>
</AnnualCount>
</A100>
<Z200 versionID="B12">
<Metadata>
<TY_Year>2020</TY_Year>
<Benefit>true</Benefit>
</Metadata>
<Z200_Ratio>
<Stratification>
<Pool>12-18</Pool>
</Stratification>
<Data>
<RegTotal>349</RegTotal>
<X5>12</X5>
<CountA>269</CountA>
<CountB>0</CountB>
</Data>
</Z200_Ratio>
<Z200_Ratio>
<Stratification>
<Pool>19-50</Pool>
</Stratification>
<Data>
<RegTotal>911</RegTotal>
<X5>192</X5>
<CountA>520</CountA>
<CountB>0</CountB>
</Data>
</Z200_Ratio>
<Z200_Ratio>
<Stratification>
<Pool>5-11</Pool>
</Stratification>
<Data>
<RegTotal>424</RegTotal>
<X5>20</X5>
<CountA>329</CountA>
<CountB>0</CountB>
</Data>
</Z200_Ratio>
<Z200_Ratio>
<Stratification>
<Pool>51-64</Pool>
</Stratification>
<Data>
<RegTotal>590</RegTotal>
<X5>456</X5>
<CountA>336</CountA>
<CountB>0</CountB>
</Data>
</Z200_Ratio>
</Z200>
</Measures>
</Submission>')
;WITH XMLNAMESPACES (DEFAULT 'http://www.ncqa.org/ns/2006/idss/hedis')
SELECT c.value('@id','VARCHAR(10)') AS msr
-- , x.value('@id','VARCHAR(10)') AS element
-- , x.value('(value/text())[1]','VARCHAR(10)') AS Val
--INTO #t
FROM @xml.nodes('/submission/measures') AS t(c)
CROSS APPLY t.c.nodes('/submission/measures') as f(x);
Solution
Please try the following solution.
SQL
DECLARE @xml XML =
N'<Submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Measures>
<A100 versionID="A01">
<Metadata>
<TY_Year>2020</TY_Year>
<Benefit>true</Benefit>
</Metadata>
<AnnualCount>
<Stratification>
<Pool>11-14</Pool>
</Stratification>
<Data>
<RegTotal>13071</RegTotal>
<CountA>542</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>15-18</Pool>
</Stratification>
<Data>
<RegTotal>12016</RegTotal>
<CountA>458</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>19-20</Pool>
</Stratification>
<Data>
<RegTotal>4591</RegTotal>
<CountA>129</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>2-3</Pool>
</Stratification>
<Data>
<RegTotal>5649</RegTotal>
<CountA>127</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>4-6</Pool>
</Stratification>
<Data>
<RegTotal>8995</RegTotal>
<CountA>366</CountA>
</Data>
</AnnualCount>
<AnnualCount>
<Stratification>
<Pool>7-10</Pool>
</Stratification>
<Data>
<RegTotal>11818</RegTotal>
<CountA>546</CountA>
</Data>
</AnnualCount>
</A100>
<Z200 versionID="B12">
<Metadata>
<TY_Year>2020</TY_Year>
<Benefit>true</Benefit>
</Metadata>
<Z200_Ratio>
<Stratification>
<Pool>12-18</Pool>
</Stratification>
<Data>
<RegTotal>349</RegTotal>
<X5>12</X5>
<CountA>269</CountA>
<CountB>0</CountB>
</Data>
</Z200_Ratio>
<Z200_Ratio>
<Stratification>
<Pool>19-50</Pool>
</Stratification>
<Data>
<RegTotal>911</RegTotal>
<X5>192</X5>
<CountA>520</CountA>
<CountB>0</CountB>
</Data>
</Z200_Ratio>
<Z200_Ratio>
<Stratification>
<Pool>5-11</Pool>
</Stratification>
<Data>
<RegTotal>424</RegTotal>
<X5>20</X5>
<CountA>329</CountA>
<CountB>0</CountB>
</Data>
</Z200_Ratio>
<Z200_Ratio>
<Stratification>
<Pool>51-64</Pool>
</Stratification>
<Data>
<RegTotal>590</RegTotal>
<X5>456</X5>
<CountA>336</CountA>
<CountB>0</CountB>
</Data>
</Z200_Ratio>
</Z200>
</Measures>
</Submission>';
-- DDL and sample data population, end
SELECT r.value('local-name(.)', 'VARCHAR(30)') AS [Msr]
, c.value('(Stratification/Pool/text())[1]', 'VARCHAR(30)') AS [Pool]
, c.value('(Data/RegTotal/text())[1]', 'INT') AS [RegTotal]
, c.value('(Data/X5/text())[1]', 'INT') AS [X5]
, c.value('(Data/CountA/text())[1]', 'INT') AS [CountA]
, c.value('(Data/CountB/text())[1]', 'INT') AS [CountB]
FROM @xml.nodes('/Submission/Measures/*[@versionID="B12"]') AS p(r)
CROSS APPLY p.r.nodes('Z200_Ratio') AS t(c);
Output
+------+-------+----------+-----+--------+--------+
| Msr | Pool | RegTotal | X5 | CountA | CountB |
+------+-------+----------+-----+--------+--------+
| Z200 | 12-18 | 349 | 12 | 269 | 0 |
| Z200 | 19-50 | 911 | 192 | 520 | 0 |
| Z200 | 5-11 | 424 | 20 | 329 | 0 |
| Z200 | 51-64 | 590 | 456 | 336 | 0 |
+------+-------+----------+-----+--------+--------+
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?