Query
Last updated
Last updated
This transformation will be delivered in the future
Specifies an XQuery against an instance of the xml data type. The result is of xml type. The method returns an instance of untyped XML. ()
The following example details the transformation for .query( )
CREATE TABLE xml_demo(object_col XML);
INSERT INTO xml_demo (object_col)
SELECT
'<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>';
INSERT INTO xml_demo (object_col)
SELECT
'<Root>
<ProductDescription ProductID="2" ProductName="Skate">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>';
SELECT
xml_demo.object_col.query('/Root/ProductDescription/Features/Warranty') as Warranty,
xml_demo.object_col.query('/Root/ProductDescription/Features/Maintenance') as Maintenance
from xml_demo;
Warranty | Maintenance |
----------------------------------------------+--------------------------------------------------------------------------------------+
<Warranty>1 year parts and labor</Warranty> | <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> |
<Warranty>1 year parts and labor</Warranty> | <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> |
CREATE OR REPLACE TABLE xml_demo (
object_col VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - XML DATA TYPE CONVERTED TO VARIANT ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;
INSERT INTO xml_demo (object_col)
SELECT
PARSE_XML(
'<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>');
INSERT INTO xml_demo (object_col)
SELECT
PARSE_XML(
'<Root>
<ProductDescription ProductID="2" ProductName="Skate">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>');
SELECT
XMLGET(XMLGET(XMLGET(object_col, 'ProductDescription'), 'Features'), 'Warranty') as Warranty,
XMLGET(XMLGET(XMLGET(object_col, 'ProductDescription'), 'Features'), 'Maintenance') as Maintenance
from
xml_demo;
Warranty | Maintenance |
----------------------------------------------+--------------------------------------------------------------------------------------+
<Warranty>1 year parts and labor</Warranty> | <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> |
<Warranty>1 year parts and labor</Warranty> | <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> |
No issues were found.
No related EWIs.