Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

XML Workshop 24 - Modifying XML Documents Using XQuery Part 1 Expand / Collapse
Author
Message
Posted Thursday, July 23, 2009 2:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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, '
<Employees>
<Employee name="Jacob" city="NY" Team="SQL Server"/>
<Employee name="Steve" city="FL" Team="SQL Server"/>
<Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'

INSERT INTO @t(id, data) SELECT 2, '
<Employees>
<Employee name="Jacob" city="NY" Team="SQL Server"/>
<Employee name="Steve" city="FL" Team="SQL Server"/>
<Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'

UPDATE @t
SET data.modify('
delete (Employees/Employee[@Team="SQL Server"]/@city)
')
SELECT * FROM @t



.
Post #758016
Posted Thursday, July 23, 2009 4:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 04, 2010 4:01 AM
Points: 4, Visits: 11
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
Post #758072
Posted Thursday, July 23, 2009 6:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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.

.
Post #758150
Posted Thursday, July 23, 2009 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 04, 2010 4:01 AM
Points: 4, Visits: 11
And it's possible to modify the file it's self using XQuery, that is, updating, deleting, inserting....
Post #758172
Posted Thursday, July 23, 2009 7:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Well, I would suggest using some other tools such as SSIS if you want to modify the file.

.
Post #758182
Posted Wednesday, October 21, 2009 2:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:13 PM
Points: 191, Visits: 565
I have multiple XML fields with the same schema. Is it possible to "concatenate" them in a select statement to generate a single XML field?

Table Schema:
CREATE TABLE Table1 (
ID INT
,XML1 XML
,XML2 XML )

Example:
XML Field 1:
   <Response>
<Value>Idaho</Value>
<Result>ID</Result>
<Return>50</Return>
</Response>

XML Field 2:
   <Response>
<Value>Texas</Value>
<Result>TX</Result>
<Return>50</Return>
</Response>

Resulting XML Field:
   <Response>
<Value>Texas</Value>
<Result>TX</Result>
<Return>50</Return>
</Response>
<Response>
<Value>Texas</Value>
<Result>TX</Result>
<Return>50</Return>
</Response>

Post #806811
Posted Wednesday, October 21, 2009 8:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Is it what you are looking for?
SELECT (
SELECT XML1, XML2
FROM table1
FOR XML PATH(''), TYPE
).query('/*/Response')
/*
<Response>
<Value>Idaho</Value>
<Result>ID</Result>
<Return>50</Return>
</Response>
<Response>
<Value>Texas</Value>
<Result>TX</Result>
<Return>50</Return>
</Response>
*/



.
Post #806923
Posted Thursday, February 18, 2010 1:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:13 PM
Points: 191, Visits: 565
Sorry for the late reply. That worked for me. Thanks.
Post #868466
Posted Thursday, March 04, 2010 5:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 05, 2014 11:11 AM
Points: 6, Visits: 98
DECLARE @XML XML
DECLARE @ElementName VARCHAR(20)
SET @ElementName = 'city'
SELECT @XML = '
<employees>
<employee>
<name>Jacob</name>
<city>NY</city>
<team>SQL Server</team>
</employee>
<employee>
<name>Steve</name>
<city>NY</city>
<team>ASP.NET</team>
</employee>
</employees>'

--SET @XML.modify('delete (/employees/employee/city)')
--SELECT @XML


But I need to delete all elements with the name equal @ElementName = 'city' and
the name of the element should be variable.
Is it possible?

Thanks, M
Post #876726
Posted Thursday, March 04, 2010 5:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 05, 2014 11:11 AM
Points: 6, Visits: 98
It seems to me that I found the solution:

DECLARE @XML XML
DECLARE @ElementName VARCHAR(20)
SET @ElementName = 'city'
SELECT @XML = '
<employees>
<employee>
<name>Jacob</name>
<city>NY</city>
<team>SQL Server</team>
</employee>
<employee>
<name>Steve</name>
<city>NY</city>
<team>ASP.NET</team>
</employee>
</employees>'

--SET @XML.modify('delete (/employees/employee/*[local-name() = "city"])')
SET @XML.modify('delete (/employees/employee/*[local-name() = sql:variable("@ElementName")])')
SELECT @XML
Post #876733
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse