6

I borrowed some code on how to compact and uncompact a string of diagnosis codes in order to build a bridge table for my data warehouse. It works well. However, I just do not understand how it is doing the uncompacting. Here Is the SQL Fiddle for the below code

create table dimDiagnosisGroup (dxGroupKey int, dxCodeList nvarchar(1024))
insert into dimDiagnosisGroup
values  (1,'042, 070.70, 722.10'),
        (2,'042, 070.70, 780.52, 496, 716.90, 581.9'),
        (3,'042, 070.70, 782.1, V58.69'),
        (4,'042, 070.70, 782.3, V58.69')


 WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
            AS Diagnosis_Code_List
     FROM dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)

I understand the XMLTaggedList part fine. What I am not understanding is the Cross Apply to the ExtractedDiagnosisList(X), then the ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)').

When I hover over ExtractedDiagnosisList in the select statement, SSMS says it is a derived table. However, it kind of looks like a function to me. I am not understanding how Diagnosis_Code.List gets the .nodes('//I') function. Finally, the ExtractedDiagnosisList.X.value section just looks foreign to me, in SQL. It looks like syntax from a language like C#.

7

What you`re seeing is the XQuery implementation in SQL Server . Although XQuery uses its own parser and performs its own algebrarization during the query compilation stage, the results are combined and optimized together with the DML portion of the query, then combined into a single execution plan.

SQL Server supports five different methods. value , exist , query , and nodes are used to access and transform the data. Last one, modify , uses XML DML to modify the data.

The value() method returns a scalar value from the XML instance Lets say you have the xml : `

declare @X xml =
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>’

select @X.value('/Order[1]/Customer[1]/@Id','int')`or
select @X.value('(/Order/Customer/@Id)[1]','int')

Would give you ID of the first customer from the first order

In your particular case value('.') means give me all values from the shredded element( that i will talk about in a moment) Remember in using value function you`re moving through XML. Now to make it more easier ,not to 'move' too much you can use function node which shreds XML into relational data. It returns a row set with rows representing the nodes identified by the path expression.

Example:

declare @X xml =
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>'


select C.t.value('(@Id)[1]','int')
from  @X.nodes('/Order/Customer') as C(t)

When you use the nodes() method with the XML column from the table, you must use the APPLY operator.

Example:

declare @X as table (xmlCode varchar(1000))
insert into @X values (
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>');

WITH CTE AS(
select CAST(xmlCode as XML) as X from @X
)
select C.t.value('(@Id)[1]','int')
from  CTE
CROSS APPLY X.nodes('/Order/Customer') as C(t)

I used examples on value and node, because you provided the code with those two functions only, if you want to read more about it please visit this

Hope this simple examples give you an idea of how to query xml types

  • So your examples look like XPATH, which I am familiar with. Same? Similar? – Anthony Genovese Jun 28 '17 at 19:32
  • Solved my own answer here: docs.microsoft.com/en-us/sql/xquery/… "Based on XPATH BUT BETTER!" – Anthony Genovese Jun 28 '17 at 19:37
  • Exactly! XPATH will help you learn how to navigate through the elements and breakdown any XML into relational data. Navigating itself is not a problem, rather syntax and how to put it all together, which using simple examples and moving forward can make it much easier for you. – S4V1N Jun 28 '17 at 19:38
  • 1
    Well this is exactly what i`ve just showed you :) – S4V1N Jun 28 '17 at 19:40
  • It was one link away :P – Anthony Genovese Jun 28 '17 at 20:04
3

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element <I></I> for the XML arrays constructed within the XMLTaggedList CTE statement. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group.

I find it easier to digest if I break up the query. Maybe you will as well.

DECLARE @dimDiagnosisGroup TABLE (dxGroupKey int, dxCodeList nvarchar(1024))
insert into @dimDiagnosisGroup
values  (1,'042, 070.70, 722.10'),
        (2,'042, 070.70, 780.52, 496, 716.90, 581.9'),
        (3,'042, 070.70, 782.1, V58.69'),
        (4,'042, 070.70, 782.3, V58.69')

-- Construct the XML arrays, this is what's going on within the CTE
SELECT dxGroupKey,
CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
    AS Diagnosis_Code_List
FROM @dimDiagnosisGroup

-- Extract all elements for each group
 ;WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
            AS Diagnosis_Code_List
     FROM @dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            -- Extract the Value from the <I></I> element and cast it as a VARCHAR(MAX)
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        -- Seperate each <I></I> element into it's own row
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)
2

An addendum to John Eisbrener's answer:

As far as the ExtractedDiagnosisList(X) part, it might help some of us if there as a space in it:

ExtractedDiagnosisList (X)

From the SQL docs, derived tables (and rowset functions, and @variable. function_calls) allow you to not only specify a table alias, but a list of column aliases for the columns returned in the "table" in question. In this case, there's one column, and we're going to call it X.

See?

[ FROM { } [ ,...n ] ]
::=
{

...

| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]

...

| derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]

...

| @variable.function_call ( expression [ ,...n ] )
[ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]

...

  • Well now it is starting to make sense. Esp with what John said – Anthony Genovese Jun 28 '17 at 19:14
  • 1
    Yes, I just updated to note that my answer was an addendum to John's - solving the remaining small mystery after he dealt with the big one. – RDFozz Jun 28 '17 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.