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»»»

More Advanced XML Processing Examples Expand / Collapse
Author
Message
Posted Tuesday, May 15, 2007 10:33 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
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2996.asp

.
Post #366062
Posted Wednesday, June 6, 2007 4:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 21, 2011 8:22 AM
Points: 11, Visits: 20
Cheers man, great article.
Post #371573
Posted Wednesday, June 6, 2007 7:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2008 7:25 AM
Points: 7, Visits: 20

Excellent Article, would love to see a cursor/loop example too.

 

Post #371620
Posted Wednesday, June 6, 2007 7:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 25, 2009 9:32 AM
Points: 44, Visits: 80

Awesome...!!!I had been looking for this kind of example at one place from a long time..u heard me..!!!

Thanks a ton....

 

Post #371640
Posted Wednesday, June 6, 2007 7:40 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
I am glad to know that the information was helpful. There are a few more posts scheduled and will appear in the coming weeks. So keep a watch

.
Post #371645
Posted Wednesday, June 13, 2007 1:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:53 AM
Points: 5, Visits: 83
thanks - given me lots of ideas where this could be useful.
Post #373373
Posted Friday, September 5, 2008 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 25, 2009 9:09 AM
Points: 2, Visits: 19
Great Articles, and we have incorporated from your articles into our projects!!!

I have a question on when our .net apps creates the xml to pass into our stored procedure, sometimes an element may not have a value which is fine, and we want to shred this xml into a relational table, but the empty element seems to be handled as an empty string versus a null. We want it to be null.

How we do it today:

DECLARE @x XML
SELECT @x = '
Employee
ContactInfo
FirstName Bob /FirstName
LastName /
/ContactInfo
/Employee'

SELECT
x.value('(FirstName)[1]','varchar(30)'),
x.value('(LastName)[1]','varchar(30)')
FROM @x.nodes('/Employee/ContactInfo') n(x)


Then we would use something like this from a our temp table to clean up data if it is empty string and replace with Null value.

IF @lastName = '' SET @lastName = NULL

So if you our xml has many parameters, our stored proc gets peppered with all these statements, so I am just looking for a better or clean way to do this?

Thanks,
Antonio
Post #564752
Posted Friday, September 5, 2008 12:09 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
Antonio,
You might need to work with TYPED XML to achieve this. You need to create a schema collection and set the element to be "nillable". Then in your XML instance, you need to quality the element with "xsi:nil" attribute to indicate that the value of element is NULL.

Here is an example [replace square brackets with xml tags before you run it ]
CREATE XML SCHEMA COLLECTION NullTest AS '
[xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"]
[xsd:element name="Employee"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="ContactInfo"]
[xsd:complexType]
[xsd:sequence]
[xsd:element name="FirstName" type="xsd:string"/]
[xsd:element name="LastName" nillable="true"
type="xsd:string"/]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:sequence]
[/xsd:complexType]
[/xsd:element]
[/xsd:schema]'
GO
DECLARE @x XML(NullTest)
SELECT @x = '
[Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"]
[ContactInfo]
[FirstName]Bob[/FirstName]
[LastName xsi:nil="true"/]
[/ContactInfo]
[/Employee]'

SELECT
x.value('(FirstName)[1]','varchar(30)') AS FirstName,
x.value('(LastName)[1]','varchar(30)') AS LastName
FROM @x.nodes('/Employee/ContactInfo') n(x)

/*
FirstName LastName
------------------------------ ------------------------------
Bob NULL
*/



.
Post #564832
Posted Friday, September 5, 2008 1:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 25, 2009 9:09 AM
Points: 2, Visits: 19
Very Nice, Thanks :D
Post #564867
Posted Friday, September 5, 2008 1:19 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
Welcome

.
Post #564869
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse