lyft_detect_xml_sql

This function helps in automatically converting XML into relational format by preparing sql with XML parsing.  It will detect XML type data for the first column in the provided Sql statement and convert into XPaths which is further consolidated in an Sql Select statement which is returned as an output to the user. Users can then use this statement to query the data from that specific table. 

Syntax

declare @result nvarchar(max);
exec lyft_detect_xml_sql 
@query=’your query’
@return_xml_sql=1, @row_set=1, @xml_value_sql = @result
select @result

Arguments

'@query'

Is a character expression of type nvarchar. The expression is used to provide a sql query with one column having XML data type.

'@return_xml_sql'

Is a character expression of type nvarchar. The expression is used to tell the Lyftrondata app to return the data in the Sql Select statement.

‘@row_set’

Is a character expression of type nvarchar. The expression is used to provide the top rows that should be used to create schema from XML for sql statement creation. 

Return types

nvarchar(max)

Example : Selecting xml from table field

Step 1: Select Xml from the table field as shown below

select UserXml from [adventureWorks2012].[dbo].[dbo.XmlTbl]

UserXml

-------

<EmployeeDetails>
   <BusinessEntityID>3</BusinessEntityID>
   <NationalIDNumber>509647174</NationalIDNumber>
   <Gender>M</Gender>
   <StoreDetail>
      <Store>
         <StoreName>Store-1</StoreName>
         <AnnualSales>800000</AnnualSales>
      </Store>
      <Store>
         <StoreName>Store-2</StoreName>
         <AnnualSales>300000</AnnualSales>
      </Store>
   </StoreDetail>
</EmployeeDetails>

Step 2: Execute the lyft_detect_xml_sql procedure

DECLARE @result NVARCHAR(max); 
EXEC lyft_detect_xml_sql
@query='select UserXml from [adventureWorks2012].[dbo].[XmlTbl]',
@return_xml_sql=1,@row_set=100,@xml_value_sql = @result
SELECT @result;

Result

SELECT Xml_value(userxml, '/EmployeeDetails/BusinessEntityID') 
       AS 
       '/EmployeeDetails/BusinessEntityID', 
       Xml_value(userxml, '/EmployeeDetails/NationalIDNumber') 
       AS 
       '/EmployeeDetails/NationalIDNumber', 
       Xml_value(userxml, '/EmployeeDetails/Gender') 
       AS '/EmployeeDetails/Gender', 
       Xml_value(userxml, '/EmployeeDetails/StoreDetail/Store[1]/StoreName') 
       AS 
       '/EmployeeDetails/StoreDetail/Store[1]/StoreName', 
       Xml_value(userxml, '/EmployeeDetails/StoreDetail/Store[1]/AnnualSales') 
       AS 
       '/EmployeeDetails/StoreDetail/Store[1]/AnnualSales', 
       Xml_value(userxml, '/EmployeeDetails/StoreDetail/Store[1]/AnnualRevenue') 
       AS 
      '/EmployeeDetails/StoreDetail/Store[1]/AnnualRevenue', 
       Xml_value(userxml, '/EmployeeDetails/StoreDetail/Store[2]/StoreName') 
       AS 
       '/EmployeeDetails/StoreDetail/Store[2]/StoreName', 
       Xml_value(userxml, '/EmployeeDetails/StoreDetail/Store[2]/AnnualSales') 
       AS 
       '/EmployeeDetails/StoreDetail/Store[2]/AnnualSales', 
       Xml_value(userxml, '/EmployeeDetails/StoreDetail/Store[2]/AnnualRevenue') 
       AS 
       '/EmployeeDetails/StoreDetail/Store[2]/AnnualRevenue' 
FROM   [adventureWorks2012].[dbo].[xmltbl]