Post reply

Parsing the XML

  • July 9, 2013 at 9:03 am

    #395104

    #1)Here is the piece of code

    DECLARE @P XML

    SET @P = '<Movie>

    <MovieName>ABCD</MovieName><Genre>Horror</Genre>

    <MovieName>EFGH</MovieName><Genre>Sci-Fi</Genre>

    <MovieName>IJKL</MovieName><Genre>Comedy</Genre>

    </Movie>'

    How to get all the data into a recordset using Inline SQL.

    #2)

    DECLARE @P XML

    SET @P = '<Movie><MovieName>ABCD</MovieName><Genre>Horror</Genre></Movie>

    <Movie><MovieName>EFGH</MovieName><Genre>Sci-Fi</Genre></Movie>

    <Movie><MovieName>IJKL</MovieName><Genre>Comedy</Genre></Movie>'

    and also the code for this type of XML. The recordset should be same as below.

    MovieName Genre

    ABCD Horror

    EFGH Sci-Fi

    IJKL Comedy

    Any help would be appreciated. Thanks in advance.

    Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

  • July 9, 2013 at 10:56 am

    #1630902

    See if this will help you. This reply was just posted yesterday so I'll just give the link:

    Parse XML to table.

     

  • July 9, 2013 at 11:41 am

    #1630914

    Here's the code to turn the XML code in #2 into a table...

    DECLARE @Pt TABLE (x XML not null);

    INSERT INTO @Pt

    SELECT '<Movie><MovieName>ABCD</MovieName><Genre>Horror</Genre></Movie>

    <Movie><MovieName>EFGH</MovieName><Genre>Sci-Fi</Genre></Movie>

    <Movie><MovieName>IJKL</MovieName><Genre>Comedy</Genre></Movie>';

    SELECT m.value('(MovieName/text())[1]', 'varchar(20)') AS Movie,

    m.value('(Genre/text())[1]', 'varchar(20)') AS Genre

    FROM @Pt

    CROSS APPLY x.nodes('Movie') AS x(m)

    Still working on the first requirement (if I understand it correctly).

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • July 9, 2013 at 12:30 pm

    #1630933

    Thanks Alan. Worked well for me.

    Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

  • July 9, 2013 at 12:30 pm

    #1630935

    Steven Willis (7/9/2013)

    See if this will help you. This reply was just posted yesterday so I'll just give the link:

    Parse XML to table.

     

    Thanks Steven..

    Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

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

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