Value

Applies to

• SQL Server

This transformation will be delivered in the future

Description

Performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value. (value() in Transact-SQL).

Sample Source Patterns

The following example details the transformation for .value( )

SQL Server

Input

IN -> SqlServer_01.sql

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.value('(/Root/ProductDescription/@ProductID)[1]', 'int' ) as ID,
    xml_demo.object_col.value('(/Root/ProductDescription/@ProductName)[1]', 'varchar(max)' ) as ProductName,
    xml_demo.object_col.value('(/Root/ProductDescription/Features/Warranty)[1]', 'varchar(max)' ) as Warranty
from xml_demo;

Output

 ID | ProductName | Warranty               |
----+-------------+------------------------+
1   | Road Bike   | 1 year parts and labor |
2   | Skate       | 1 year parts and labor |

Snowflake SQL

Input

OUT -> SqlServer_01.sql

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
    GET(XMLGET(object_col, 'ProductDescription'), '@ProductID') :: INT as ID,
    GET(XMLGET(object_col, 'ProductDescription'), '@ProductName') :: VARCHAR as ProductName,
    GET(XMLGET(XMLGET(XMLGET(object_col, 'ProductDescription'), 'Features'), 'Warranty', 0), '$') :: VARCHAR as Warranty
from
    xml_demo;

Output

 ID | PRODUCTNAME | WARRANRTY              |
----+-------------+------------------------+
1   | Road Bike   | 1 year parts and labor |
2   | Skate       | 1 year parts and labor |

Known Issues

No issues were found.

Related EWIs

1. SSC-EWI-0036: Data type converted to another data type.