Search code examples
sql-serverxmlt-sqlxpathfor-xml-path

Creating XML from SQL Table (Unique XML formatting)

So, I'm trying to create a XML file from a SQL table. I do know of the route of using...

Select * From dbo.[db_name]
FOR XML PATH

But the issue at hand is that the XML styling/formatting is quite odd...

                    <ID>170607A13</ID>
                    <MaterialActual>
                        <MaterialLotID>170607A13</MaterialLotID>
                        <MaterialActualProperty>
                            <ID>CreationDate</ID>
                            <Value>
<ValueString>2017-06-07T12:26:27.667-05:00</ValueString>
                            </Value>
                        </MaterialActualProperty>

Therefore, I decided I could go the route of concatenating it and inserting into the XML file. Like so...

DECLARE @NAME varchar(50)
DECLARE @LOCATION varchar(50)
DECLARE @SearchXML xml

SET @SearchXML = '<Root>
            <CallerInformation>
                <LastName>' + @LOCATION + '</LastName>
                <FirstName>' + @NAME + '</FirstName>

            </CallerInformation>
        </Root>'

SELECT @SearchXML

But when doing this I get returned...

enter image description here

If I could get pointed in the right direction or even a example that would be great!

Solution

  • But the issue at hand is that the XML styling/formatting is quite odd...

    What is odd there? The only thing odd I can see is the attempt to solve this on string level...

    Your question is missing sample data and expected output. The simple select you provide tells us nothing, the XML you provide is an inclompete fragment and the actual example is something completely different...

    Just some hints:

    Your simple example should be done like this:

    DECLARE @NAME varchar(50)
DECLARE @LOCATION varchar(50)
DECLARE @SearchXML xml

SET @SearchXML = 
(
    SELECT @LOCATION AS LastName
          ,@NAME AS FirstName
    FOR XML PATH('CallerInformation'),ROOT('Root'),TYPE

);

SELECT @SearchXML;

    This will lead to an almost empty (but valid!) XML, put any value into the variables and you will see the XML filled.

    UPDATE: Your odd XML...

    Try something like this:

    SET @xml=
(
    SELECT '170607A13' AS ID
          ,'170607A13' AS [MaterialActual/MaterialLot]
          ,'CreationDate' AS [MaterialActual/MaterialActualProperty/ID]
          ,GETDATE() AS [MaterialActual/MaterialActualProperty/Value/ValueString]
    FOR XML PATH('')
);
SELECT @xml

    UPDATE 2: Very long XPath...

    This is your error: name-length more then 128

    DECLARE @xml XML;
--SET @xml=
--(
--    SELECT '170607A13' AS ID
--          ,'170607A13' AS [MaterialActual1234567890/MaterialLot1234567890]
--          ,'CreationDate' AS [MaterialActual1234567890/SomeMore1234567890/EvenMore1234567890/StillMore1234567890/MaterialActualProperty1234567890/ID1234567890]
--          ,GETDATE() AS [MaterialActual1234567890/SomeMore1234567890/EvenMore1234567890/StillMore1234567890/MaterialActualProperty1234567890/ValueString1234567890]
--    FOR XML PATH('')
--);
--SELECT @xml

    --This is a solution: nested sub-select:

    SET @xml=
(
    SELECT '170607A13' AS ID
          ,'170607A13' AS [MaterialActual1234567890/MaterialLot1234567890]
          ,(
          SELECT
          'CreationDate' AS [EvenMore1234567890/StillMore1234567890/MaterialActualProperty1234567890/ID1234567890]
          ,GETDATE() AS [EvenMore1234567890/StillMore1234567890/MaterialActualProperty1234567890/ValueString1234567890]
          FOR XML PATH('SomeMore1234567890'),TYPE
          ) AS [MaterialActual1234567890]
    FOR XML PATH('')
);
SELECT @xml;

    UPDATE 3: Your follow-up question in comment

    HINT: Avoid follow-up questions. Next time please add a new question!

    Both return the result requested:

    SELECT 'yyyy-MM-dd''T''HH:mm:ss.SSSXXX' AS [PublishedDate/@format]
      ,GETDATE() AS PublishedDate
FOR XML PATH('')

SELECT 'yyyy-MM-dd''T''HH:mm:ss.SSSXXX' AS [@format]
      ,GETDATE() AS [*]
FOR XML PATH('PublishedDate');

    In my eyes there's no need for the format. Within XML a datetime should be in this format (which is ISO8601) anyway. This is the standard format...