Post reply

XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

  • July 22, 2009 at 12:38 am

    #133201

    Comments posted to this topic are about the item XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

    .

  • July 22, 2009 at 6:08 am

    #1028230

    Hi Jacob,

    this is really a good article, very clear and friendly one.

    Please tell me, where should I write this code to get the required results from XQuery?

    In XML file? in some editor? processor?

    Just for example, in order to run C# code I should write it in .cs file extenstion and build it with .NET compiler.... or JavaScript in .js file extention and run it in some browser... etc.

    Thank you,

    Yair

  • July 22, 2009 at 7:17 am

    #1028291

    Hi Yair,

    All these are TSQL examples and hence you can run them in SQL Server Management Studio.

    regards

    Jacob

    .

  • July 22, 2009 at 9:02 am

    #1028383

    Nice article.

    Would it also be possible to look at the same processes where the XML data is arranged like...

    Jacob

    NY

    SQL Server

    Steve

    SQL Server

    Bob

    CA

    Which include blank elements.

    Have already been caught out with data like this needing insert/modify depending if a value exists

    Thanks

    Andy

  • July 22, 2009 at 9:25 am

    #1028411

    In example 10 where you delete the “city” attribute where the “team” is “SQL Server” is there any way to pass in the “SQL Server” as a variable instead of using a constant?

    Thanks,

    Marlin

  • July 22, 2009 at 10:05 am

    #1028472

    mg (7/22/2009)

    In example 10 where you delete the “city” attribute where the “team” is “SQL Server” is there any way to pass in the “SQL Server” as a variable instead of using a constant?

    Thanks,

    Marlin

    Marlin,

    You can use a variable as given in the following example

    DECLARE @x XML

    SELECT @x = '

    '

    DECLARE @Team VARCHAR(20)

    SELECT @Team = 'SQL Server'

    SET @x.modify('

    delete (Employees/Employee[@Team=sql:variable("@Team")]/@city)

    ')

    SELECT @x

    /*

    */

    .

  • July 22, 2009 at 10:10 am

    #1028479

    andrew.sims (7/22/2009)

    Nice article.

    Would it also be possible to look at the same processes where the XML data is arranged like...

    Jacob

    NY

    SQL Server

    Steve

    SQL Server

    Bob

    CA

    Which include blank elements.

    Have already been caught out with data like this needing insert/modify depending if a value exists

    Thanks

    Andy

    Hi Andy,

    It is possible by modifying the Xquery expression slightly, as given in the following example

    DECLARE @x XML

    SELECT @x = '

    Jacob

    NY

    SQL Server

    Steve

    NY

    ASP.NET

    '

    DECLARE @Team VARCHAR(20)

    SELECT @Team = 'SQL Server'

    SET @x.modify('

    delete (employees/employee[team=sql:variable("@Team")]/city)

    ')

    SELECT @x

    /*

    Jacob

    SQL Server

    Steve

    NY

    ASP.NET

    */

    .

  • July 22, 2009 at 10:51 am

    #1028521

    Your a star !!!

    Just realy getting into this XML thing with SQL server, so examples like this are perfect.

    Andy

  • July 22, 2009 at 10:55 am

    #1028525

    Glad to know it helped.

    I have written a series of XQuery tutorials at my blog site, in case you need further help.

    regards

    Jacob

    .

  • July 23, 2009 at 1:23 am

    #1028781

    Hi Jacob,

    It did work for me using the SQL query as you advised.

    Look, according to your example, we are working against an XML variable, what about working against physical XML file?

    It's possible?

    Yair

  • July 23, 2009 at 2:05 am

    #1028790

    Yes, it can work on an XML column too. Here is an example

    DECLARE @t TABLE (id int, data XML)

    INSERT INTO @t(id, data) SELECT 1, '

    '

    INSERT INTO @t(id, data) SELECT 2, '

    '

    UPDATE @t

    SET data.modify('

    delete (Employees/Employee[@Team="SQL Server"]/@city)

    ')

    SELECT * FROM @t

    .

  • July 23, 2009 at 4:19 am

    #1028833

    Hi, if your answer is intended for me then I think I didn't clear my self enough.

    I'm asking if it possible to access XML file from XQuery instead of querying an XML variable.

    Yair

  • July 23, 2009 at 6:39 am

    #1028894

    You can load the content of an XML file to an XML variable using OPENROWSET(BULK..). Once the content of the XML file is loaded into the XML variable, you can query it.

    .

  • July 23, 2009 at 6:55 am

    #1028911

    And it's possible to modify the file it's self using XQuery, that is, updating, deleting, inserting....

  • July 23, 2009 at 7:02 am

    #1028919

    Well, I would suggest using some other tools such as SSIS if you want to modify the file.

    .

Viewing 15 posts - 1 through 15 (of 21 total)

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