locked
Trying to post a XML section just once RRS feed

  • Question

  •  Hi there,

     

    I would need to define one section just once in my XML generation by using Xquery/FLWOR approach.

    Example of data -the column IT_Region does not make sense but it is illustrating what I am looking for-  where I am declaring two FOR without success, it is complaining about the 'To':


    DECLARE @tbl TABLE (Customer_id INT PRIMARY KEY, Firstname varchar(255), Lastname varchar(255), Delivery_Address_1 varchar(255),
    Delivery_Address_2 varchar(255), Delivery_City varchar(255), Delivery_State varchar(255),It_Region  varchar(12));
    
    INSERT INTO @tbl (Customer_id,Firstname,Lastname,Delivery_Address_1,Delivery_Address_2,Delivery_City,Delivery_State,It_Region)
    VALUES
    (1000,'FName','LName','Enkele Ring 9','street2','dummSommelsdijk','dummystate','APAC')
    , (1770,'John','Dow','Sullam Yakov 5','street77','Jerusalem','Israel','APAC')
    ,(2500,'Dummy1','Dummy2','StreetDummy','','Lyon','France','APAC');
    
    
    
    SELECT * FROM @tbl;
    
    SELECT (
        SELECT *
        FROM @tbl
        FOR XML PATH('r'), TYPE, ROOT('root')
    ).query('<Customers xsi:schemaLocation="http://www.demandware.com/xml/impex/customer/2020-02-21 Customer.xsd"
                  xmlns="http://www.demandware.com/xml/impex/customer/2020-02-20"
                  xmlns:n2="http://www.demandware.com/xml/impex/customer/2006-10-31">
    {
    
    
    	for $i in 1 to 1
    	return
    		<header>
    			<area>{data($x/*:It_Region)}</area>
    		</header>
    
        for $x in /*:root/*:r
        return <Customer customerkey="{data($x/*:Customer_id)}">
            <profile>
                <first-name>{data($x/*:Firstname)}</first-name>
                <second-name>{data($x/*:Lastname)}</second-name>
            </profile>
            <addresses>
                <first-name>{data($x/*:Firstname)}</first-name>
                <second-name>{data($x/*:Lastname)}</second-name>
                <address1>{data($x/*:Delivery_Address_1)}</address1>
    
               {
                         if ($x/Delivery_Address_2[text()]) then $x/Delivery_Address_2 else ()
                }
    
                <city>{data($x/*:Delivery_City)}</city>
                <country-code>{data($x/*:Delivery_State)}</country-code>
            </addresses>
        </Customer>
    }
    </Customers>');
    
    

    And this is the expected outcome:

    <Customers xmlns="http://www.demandware.com/xml/impex/customer/2020-02-20" xmlns:n2="http://www.demandware.com/xml/impex/customer/2006-10-31" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.demandware.com/xml/impex/customer/2020-02-21 Customer.xsd">
    	<header>
    		<area>APAC</area>
    	</header>
    	<Customer customerkey="1000">
        <profile>
          <first-name>FName</first-name>
          <second-name>LName</second-name>
        </profile>
        <addresses>
          <first-name>FName</first-name>
          <second-name>LName</second-name>
          <address1>Enkele Ring 9</address1>
          <city>dummSommelsdijk</city>
          <country-code>dummystate</country-code>
        </addresses>
      </Customer>
      <Customer customerkey="1770">
        <profile>
          <first-name>John</first-name>
          <second-name>Dow</second-name>
        </profile>
        <addresses>
          <first-name>John</first-name>
          <second-name>Dow</second-name>
          <address1>Sullam Yakov 5</address1>
          <city>Jerusalem</city>
          <country-code>Israel</country-code>
        </addresses>
      </Customer>
      <Customer customerkey="2500">
        <profile>
          <first-name>Dummy1</first-name>
          <second-name>Dummy2</second-name>
        </profile>
        <addresses>
          <first-name>Dummy1</first-name>
          <second-name>Dummy2</second-name>
          <address1>StreetDummy</address1>
          <city>Lyon</city>
          <country-code>France</country-code>
        </addresses>
      </Customer>
    </Customers>
    

    Thanks a lot for your help,

    Enric

    Wednesday, April 1, 2020 10:07 AM

Answers

  • Hi Enric,

    It is relatively easy to achieve via XQuery/FLWOR powers.

    • You just need to put the static fragment just before the for $... in... construct.
    • I slightly modified a check for the address2 NULL/empty values.

    SQL:

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (Customer_id INT PRIMARY KEY, Firstname varchar(255), Lastname varchar(255), Delivery_Address_1 varchar(255),
    Delivery_Address_2 varchar(255), Delivery_City varchar(255), Delivery_State varchar(255), It_Region VARCHAR(12));
    
    INSERT INTO @tbl (Customer_id,Firstname,Lastname,Delivery_Address_1,Delivery_Address_2,Delivery_City,Delivery_State,It_Region)
    VALUES
    (1000,'FName','LName','Enkele Ring 9','street2','dummSommelsdijk','dummystate','APAC')
    , (1770,'John','Dow','Sullam Yakov 5','street77','Jerusalem','Israel','APAC')
    , (2500,'Dummy1','Dummy2','StreetDummy','','Lyon','France','APAC')		-- empty string
    , (2555,'Dummy1','Dummy2','StreetDummy',NULL,'Lyon','France','APAC');	-- NULL value
    -- DDL and sample data population, end
    
    SELECT * FROM @tbl
    FOR XML PATH('r'), TYPE, ROOT('root');
    
    SELECT (
    	SELECT * 
    	FROM @tbl
    	FOR XML PATH('r'), TYPE, ROOT('root')
    ).query('<n2:Customers xsi:schemaLocation="http://www.demandware.com/xml/impex/customer/2020-02-21 Customer.xsd"
                  xmlns="http://www.demandware.com/xml/impex/customer/2020-02-20"
    			  xmlns:n2="http://www.demandware.com/xml/impex/customer/2006-10-31">
    	<header>
    		<area>{data(/*:root/*:r[1]/*:It_Region)}</area>
    	</header>
    {
    	for $x in /*:root/*:r
    	return 
    	<n2:Customer customerkey="{data($x/*:Customer_id)}">
    		<n2:profile>
    			<n2:first-name>{data($x/*:Firstname)}</n2:first-name>
    			<n2:second-name>{data($x/*:Lastname)}</n2:second-name>
    		</n2:profile>
    		<n2:addresses>
    			<n2:first-name>{data($x/*:Firstname)}</n2:first-name>
    			<n2:second-name>{data($x/*:Lastname)}</n2:second-name>
    			<n2:address1>{data($x/*:Delivery_Address_1)}</n2:address1>
    			{
    			   (: if ($x/*:Delivery_Address_2[text()]) then :)
    			   if ($x/*:Delivery_Address_2[string-length(text()[1]) gt 0]) then
    				  <n2:address2>{data($x/*:Delivery_Address_2)}</n2:address2>
    			   else ()
    			}
    			<n2:city>{data($x/*:Delivery_City)}</n2:city>
    			<n2:country-code>{data($x/*:Delivery_State)}</n2:country-code>
    		</n2:addresses>
    	</n2:Customer>
    }
    </n2:Customers>');

    Wednesday, April 1, 2020 12:37 PM