Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More Advanced XML Processing Examples


More Advanced XML Processing Examples

Author
Message
jacob sebastian
jacob sebastian
Mr or Mrs. 500
Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)

Group: General Forum Members
Points: 588 Visits: 2523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2996.asp

.
claytons smith
claytons smith
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 20
Cheers man, great article.
Alan Robbins-417146
Alan Robbins-417146
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 20

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


bytezone
bytezone
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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....


jacob sebastian
jacob sebastian
Mr or Mrs. 500
Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)

Group: General Forum Members
Points: 588 Visits: 2523
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 :-)

.
alex pilsworth
alex pilsworth
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 97
thanks - given me lots of ideas where this could be useful.
antonio.j.olander
antonio.j.olander
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
jacob sebastian
jacob sebastian
Mr or Mrs. 500
Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)

Group: General Forum Members
Points: 588 Visits: 2523
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
*/



.
antonio.j.olander
antonio.j.olander
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 19
Very Nice, Thanks BigGrin
jacob sebastian
jacob sebastian
Mr or Mrs. 500
Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)

Group: General Forum Members
Points: 588 Visits: 2523
Welcome :-)

.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search