This function helps in converting XML into relational format by preparing a relational format with values as an output. It will detect XML type data for the first column in the provided Sql statement and convert into XPaths which are further evaluated by query analyzer. The schema is built from the table having highest value of XML data and returns the row from the table having highest XML data.
Syntax
declare @result nvarchar(max);
exec lyft_detect_xml_value
@query=’your query’
@return_xml_value=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_value'
Is a character expression of type nvarchar. The expression is used to tell the Lyftrondata app to return the data in relational format.
‘@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.
‘@file’
Is a character expression of type nvarchar. The value of this attribute should be 1 when data is not from table and from a file source.
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_value procedure
DECLARE @result NVARCHAR(max);
EXEC lyft_detect_xml_value
@query='select UserXml from [adventureWorks2012].[dbo].[XmlTbl]',
@return_xml_value=1,@row_set=100,@xml_value_sql=@result;
SELECT @result;
Result
Example 2: Selecting xml from file
DECLARE @result NVARCHAR(max);
EXEC lyft_detect_xml_value
@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_value=1,@file=1,@row_set=100,@xml_value_sql=@result;
SELECT @result;
Result
