XML element value with width limitation

Question

I am new to XML and trying to solve the following in SQL Server 2008 using customer table.

NAME column has fixed width, so the value (customer name) needs to be separated into more than one representation.

Please see:

• NAME index="1"....
• NAME index="2"....

Any idea how to tackle this?

Thank you, Anne

<PARTNER>
    <NAME index="1">XEXSY SMALL REALTY LLC</NAME>
    <NAME index="2">AA/NAX TEEEENERGY</NAME>
    <PARTNRTYPE>703884</PARTNRTYPE>
    <ADDRESS>
       <ADDRLINE index="1">544 PACIFIC BLVD</ADDRLINE>
       <CITY>LONG BEACH</CITY>
       <COUNTRY>US</COUNTRY>
       <POSTALCODE>07740</POSTALCODE>
    </ADDRESS>
</PARTNER>

Answer 1

This design is awfull. If you have to the slightest chance to change this, you should...

If you have to stick with this, you can try it like this:

DECLARE @mockup TABLE(Name VARCHAR(100),PartnerType INT,Addr VARCHAR(100),City VARCHAR(100));
INSERT INTO @mockup VALUES
 ('This is a very long name which needs to be splitted in smaller parts'
  ,12345
  ,'And this address is very long too, the person has a really long address...'
  ,'Washington')
,('ShortName'
  ,12345
  ,'ShortAddress'
  ,'New York');

--You can set the length of the slices. The TOP(20) is hardcoded and sets an upper limit to the count of parts.

DECLARE @PartLenght INT=20;

--The query will get a number's table (tally table) on-the-fly and then use FOR XML PATH() to create the XML with the nestings you need.

WITH Tally AS
(
    SELECT TOP(20) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nr 
    FROM master..spt_values 
)
SELECT (
        SELECT Nr AS [NAME/@index]
              ,SUBSTRING(m.Name,Nr+((Nr-1) * (@PartLenght-1)),@PartLenght) AS [NAME]
        FROM Tally
        WHERE LEN(SUBSTRING(m.Name,Nr+((Nr-1) * (@PartLenght-1)),@PartLenght))>0
        FOR XML PATH(''),TYPE
       )
      ,m.PartnerType AS [PARTNERTYPE]
      ,(
          SELECT    
          (
            SELECT Nr AS [ADDRLINE/@index]
                  ,SUBSTRING(m.Addr,Nr+((Nr-1) * (@PartLenght-1)),@PartLenght) AS [ADDRLINE]
            FROM Tally
            WHERE LEN(SUBSTRING(m.Addr,Nr+((Nr-1) * (@PartLenght-1)),@PartLenght))>0
            FOR XML PATH(''),TYPE
          )
         ,City AS [CITY]
         FOR XML PATH('ADDRESS'),TYPE
      )
FROM @mockup AS m
FOR XML PATH('PARTNER')

The result

<PARTNER>
  <NAME index="1">This is a very long </NAME>
  <NAME index="2">name which needs to </NAME>
  <NAME index="3">be splitted in small</NAME>
  <NAME index="4">er parts</NAME>
  <PARTNERTYPE>12345</PARTNERTYPE>
  <ADDRESS>
    <ADDRLINE index="1">And this address is </ADDRLINE>
    <ADDRLINE index="2">very long too, the p</ADDRLINE>
    <ADDRLINE index="3">erson has a really l</ADDRLINE>
    <ADDRLINE index="4">ong address...</ADDRLINE>
    <CITY>Washington</CITY>
  </ADDRESS>
</PARTNER>
<PARTNER>
  <NAME index="1">ShortName</NAME>
  <PARTNERTYPE>12345</PARTNERTYPE>
  <ADDRESS>
    <ADDRLINE index="1">ShortAddress</ADDRLINE>
    <CITY>New York</CITY>
  </ADDRESS>
</PARTNER>