lyft_detect_xml_paths

This function helps in converting provided XML into XPaths. It will detect XML type data for the first column in the provided Sql statement and convert into XPaths which is then output to the user in relational format. The schema is built from the table having highest value of XML data and returns the row from the table having highest XML data.

The user will see the output having Json paths in a table format as an end result.

Syntax

declare @result nvarchar(max);
exec lyft_detect_xml_paths 
@query=’your query’
@return_xml_paths=1,@xml_paths_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_paths'

Is a character expression of type nvarchar. The expression is used to tell the Lyftrondata app to return the data in XML paths.

Return types

nvarchar(max)

Example 1: 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_paths procedure

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

Result

1/EmployeeDetails/BusinessEntityID
2/EmployeeDetails/NationalIDNumber
3/EmployeeDetails/Gender
4/EmployeeDetails/StoreDetail/Store[1]/StoreName
5/EmployeeDetails/StoreDetail/Store[1]/AnnualSales
6/EmployeeDetails/StoreDetail/Store[2]/StoreName
7/EmployeeDetails/StoreDetail/Store[2]/AnnualSales
8/EmployeeDetails/StoreDetail/Store[1]/AnnualRevenue
9/EmployeeDetails/StoreDetail/Store[2]/AnnualRevenue

Example 2: Selecting xml from file

DECLARE @result NVARCHAR(max); 
EXEC Lyft_detect_xml_paths 
  @query='select ''<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>''',
@return_xml_paths=1,@xml_value_sql=@result;
SELECT @result; 

Result

1/EmployeeDetails/BusinessEntityID
2/EmployeeDetails/NationalIDNumber
3/EmployeeDetails/Gender
4/EmployeeDetails/StoreDetail/Store[1]/StoreName
5/EmployeeDetails/StoreDetail/Store[1]/AnnualSales
6/EmployeeDetails/StoreDetail/Store[2]/StoreName
7/EmployeeDetails/StoreDetail/Store[2]/AnnualSales
8/EmployeeDetails/StoreDetail/Store[1]/AnnualRevenue
9/EmployeeDetails/StoreDetail/Store[2]/AnnualRevenue