|
|
|
SSC-Addicted
 
Group: General Forum Members
Last Login: Friday, November 06, 2009 1:12 PM
Points: 414,
Visits: 2,250
|
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, February 23, 2009 4:04 AM
Points: 11,
Visits: 10
|
|
| Cheers man, great article.
|
|
|
|
|
Forum 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.
|
|
|
|
|
SSC 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....
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 06, 2009 1:12 PM
Points: 414,
Visits: 2,250
|
|
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 
Jacob Sebastian, SQL Server MVP
http://beyondrelational.com/blogs/jacob/
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, July 08, 2009 3:01 AM
Points: 3,
Visits: 43
|
|
| thanks - given me lots of ideas where this could be useful.
|
|
|
|
|
Forum 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
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 06, 2009 1:12 PM
Points: 414,
Visits: 2,250
|
|
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
*/
Jacob Sebastian, SQL Server MVP
http://beyondrelational.com/blogs/jacob/
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, March 25, 2009 9:09 AM
Points: 2,
Visits: 19
|
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 06, 2009 1:12 PM
Points: 414,
Visits: 2,250
|
|
|
|
|
