XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

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

    .

  • 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

  • Hi Yair,

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

    regards

    Jacob

    .

  • 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

  • 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

  • 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

    /*

    */

    .

  • 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

    */

    .

  • Your a star !!!

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

    Andy

  • 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

    .

  • 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

  • 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

    .

  • 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

  • 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.

    .

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

  • 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