How to get these description from XML into the table?

  •  

    I want all these description in different row. TIA

    Example

    descriptions

    0|Home

    1|Office|False

    2|Play Ground

    3|School|True

    4|Movie Hall

    DECLARE @XMLData XML ,@Ret INT

    SET @XMLData ='<PlaceDescriptions>

    <descriptions>0|Home</descriptions>

    <descriptions>1|Office|False</descriptions>

    <descriptions>2|Play Ground</descriptions>

    <descriptions>3|School|True</descriptions>

    <descriptions>4|Movie Hall</descriptions>

    ></PlaceDescriptions>'

    declare @docHandle int

    EXEC @Ret = sp_xml_preparedocument @docHandle OUTPUT, @XMLData;

    SELECT *

    FROM OPENXML(@docHandle, '/PlaceDescriptions/descriptions',3)

    WITH ( descriptions VARCHAR(100) '../@descriptions');

    SELECT

    doc.col.value('descriptions[1]', 'nvarchar(10)') descriptions

    FROM @XMLData.nodes('/PlaceDescriptions/descriptions') doc(col)

    • This topic was modified 1 month ago by  NewDBA201.
  • Firstly, you don't need to use sp_xml_preparedocument and OPENXML.

    Secondly, your xml is not properly formed - There is an extra ">" before "</PlaceDescriptions>"

    Below is code that will extract the descriptions from the sample.  NOTE:  I have kept the data size as nvarchar(10), but it will truncate some of the values.  You may want to increase it to nvarchar(20).  Also, Do you really need nvarchar?  The provided sample data contains no unicode.

    DECLARE @XMLData xml;

    SET @XMLData = '<PlaceDescriptions>
    <descriptions>0|Home</descriptions>
    <descriptions>1|Office|False</descriptions>
    <descriptions>2|Play Ground</descriptions>
    <descriptions>3|School|True</descriptions>
    <descriptions>4|Movie Hall</descriptions>
    </PlaceDescriptions>';

    SELECT descriptions = doc.col.value( '(text())[1]', 'nvarchar(10)' )
    FROM @XMLData.nodes('/PlaceDescriptions/descriptions') AS doc(col);

     

    • This reply was modified 1 month ago by  DesNorton.
  • You can take this further and split the rows into columns like this

    DECLARE @XMLData xml;

    SET @XMLData = '<PlaceDescriptions>
    <descriptions>0|Home</descriptions>
    <descriptions>1|Office|False</descriptions>
    <descriptions>2|Play Ground</descriptions>
    <descriptions>3|School|True</descriptions>
    <descriptions>4|Movie Hall</descriptions>
    </PlaceDescriptions>';

    WITH cteXML AS (
    SELECT descriptions = doc.col.value( '(text())[1]', 'varchar(20)' )
    FROM @XMLData.nodes('/PlaceDescriptions/descriptions') AS doc(col)
    )
    SELECT cte.descriptions
    , [id] = MAX(CASE WHEN stm.ItemNumber = 1 THEN stm.Item END)
    , [description] = MAX(CASE WHEN stm.ItemNumber = 2 THEN stm.Item END)
    , [value] = MAX(CASE WHEN stm.ItemNumber = 3 THEN stm.Item END)
    FROM cteXML AS cte
    CROSS APPLY dbo.DelimitedSplit8K_LEAD(cte.descriptions, '|') AS stm
    GROUP BY cte.descriptions;

    The code for the splitter can be found here

    http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/

  • Thank you so much.

  • DesNorton wrote:

    You can take this further and split the rows into columns like this

    DECLARE @XMLData xml;

    SET @XMLData = '<PlaceDescriptions>
    <descriptions>0|Home</descriptions>
    <descriptions>1|Office|False</descriptions>
    <descriptions>2|Play Ground</descriptions>
    <descriptions>3|School|True</descriptions>
    <descriptions>4|Movie Hall</descriptions>
    </PlaceDescriptions>';

    WITH cteXML AS (
    SELECT descriptions = doc.col.value( '(text())[1]', 'varchar(20)' )
    FROM @XMLData.nodes('/PlaceDescriptions/descriptions') AS doc(col)
    )
    SELECT cte.descriptions
    , [id] = MAX(CASE WHEN stm.ItemNumber = 1 THEN stm.Item END)
    , [description] = MAX(CASE WHEN stm.ItemNumber = 2 THEN stm.Item END)
    , [value] = MAX(CASE WHEN stm.ItemNumber = 3 THEN stm.Item END)
    FROM cteXML AS cte
    CROSS APPLY dbo.DelimitedSplit8K_LEAD(cte.descriptions, '|') AS stm
    GROUP BY cte.descriptions;

    The code for the splitter can be found here

    http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/

    Niiiccceee  followup and great assumption as to what the next logical question might be.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply