The Heap™ – Consultancy ©®

11:08 AM

I'm looking for the best solution to store XML data. As far as I can see, I should probably look for a document/NoSQL database, but I'd love suggestions.

ypercubeᵀᴹ

@WilliamMariager check this, too: dba.stackexchange.com/questions/142094/xml-database-for-linux/…

Thanks!

Since I'm already in the Windows domain, SQL Server looks like the obvious choice.

ypercubeᵀᴹ

@WilliamMariager I think all products run in Windows.

But as the guy who asked that question mentioned, Postgres doesn't fully support xquery, so it may not be the best choice.

And to be honest, yeah, Oracle and Postgres are more often to see them in Linux and Unix systems

Yeah.

I'm also working with C# and Visual Studio, which has a good designer to help with all the queries if I use SQL Server.

Now I just need to see if I can figure out how to automate table setup from XSD or XML definitions. It won't be completely bad if I can't, but it'd save me a lot of work.

wBob

12:10 PM

@WilliamMariager There is an old library that does that, complete schemas including (FKs) from XSDs

How to import XML into SQL Server with the XML Bulk Load component
http://support.microsoft.com/kb/316005

The XSDs are naturally tricky to set up.

How to create tables in a database using an XSD schema and SQLXMLBulkload
http://blogs.msdn.com/b/monicafrintu/archive/2007/06/12/how-to-create-tables-in-a-database-using-an-xsd-schema-and-sqlxmlbulkload.aspx

A bit "old school" but still a powerful technique.

Thanks, I'll give it a look. :) I can't change the XSDs so I guess I just have to hope for the best.

James Anderson

1:56 PM

Can't seem to post a question. Is there an issue with the site?

so im pretty sure i have some "bad" characters

i need to find out what i have

char(13), etc

i would like to do a replace() when i find the bad characters - however i dont know what bad characters i have

this is SQL server. how can i accomplish this?

well, how could we know if we don't even understand what "bad" characters mean?

Q: How to strip all non-alphabetic characters from string in SQL Server?

fair enough

all i want is number values 0-9 and/or a "." character

everything else i need stripped out.

i dont want to use a regex in a where clause because i still need the records that may contain other characters

so ex of a bad character would be a return character. i think that is char(13)

Colin 't Hart

96

How could you remove all characters that are not alphabetic from a string? What about non-alphanumeric? Does this have to be a custom function or are there also more generalizable solutions?

Should be able to rework that to suit what you want.

Q: How can I strip non-numeric characters out of a string?

sorry, don't really follow. "all i want is number values 0-9 and/or a "." character
everything else i need stripped out." and then "
i dont want to use a regex in a where clause because i still need the records that may contain other characters"

whic is it

7

Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type. One field (phone number) consists of all numbers, so when checking it strips out all non-numeric cha...

sql-server sql-server-2005 t-sql

Colin 't Hart

2:03 PM

@Lamak I interpreted that to mean that he wants the "strip" done in the select clause, not the where clause.

correct @Colin'tHart

@Colin'tHart ah, that makes sense

Tom V

2:17 PM

@JamesAnderson Didn't try actually posting anything but I do get the web form

James Anderson

@TomV Looks like it was something to do with the code in my question

I've linked to a gist instead

is there a regex for "any number of digits" ?

i can only seem to match numbers with specific counts of digits

what do you mean?

3:06 PM

i have values like 0.2, 23.2, 23.34, etc i want to pick up all of them and exclude any return characters or whatever other junk may be there

so my thought was to match <any number of digits between 0-9>\.<any # of digitz 0-9>

[0-9] only matches a single digit.

i dont really want do to [0-9]|[0-9][0-9]|/. etc..

so isnt there a way to say "match a range of digits of unspecified length" ?

@JzInqXc9Dg There is a regex for that but if you mean whether you can do that in T-SQL with its crippled regex support, then no.

A complex condition like this might do the job: @n LIKE '%[0-9]%.%[0-9]%' AND @n NOT LIKE '%[^0-9]%.%[^0-9]%'.

(not tested)

Perhaps the NOT LIKE part alone would be enough.

Although it would then match values like 123., .123 and . (in addition to 123.123).

really, my actual problem is i have this varchar column i need to convert to decimal. however, it seems to fail on some row(s) - i think this is because there is some extra "junk" in there. i know if i copy the values without trying to convert, i do see a return character. so i've then done a replace(), replacing char(13) with '' - this gets rid of those return characters.... but im still getting a conversion error

What's up folks?

so im guessing there may be some other "bad" characters. thus, i want to just "find all bad characters" in a query and see what i got.

3:21 PM

Hi, @Zane

so then my thought was to get to record with "bad" chars by using a regex to ensure i can grab pristine values

@mmarie When are you rolling back into town?

@Zane TBD. Possibly August for St Cloud again. I was in Mankato a few weeks ago.

@Zane you coming to SQL Sat KC this year?

Can't....

:-(

3:25 PM

Trying to save to buy a house.

Ah. I'm considering starting to do that myself.

But still going to SQL Sat KC

I might decide to go last minute but we'll see.

Obviously, I dont' have a house there anymore, but I am probably getting a AirBNB house. My dog is coming with me.

Nice.

So if you decide to come, you can probably crash in my AirBNB

I'm just going to spend the week there.

3:33 PM

You're still helping to run it so that makes sense.

Yeah I'll see. Let me look up the event day.

Call for speakers is still open until July?

@JzInqXc9Dg As part of investigation, you could return your varchar values converted to varbinary to see the character codes. I would probably first strip the good characters and leave the bad ones for observation, to make things easier.

Shit maybe I should write a new presentation.

@Zane Yep. July 26. I think you should write a new presentation. :)

@AndriyM but even the stripping the good chars.... ive basically been trying to do this. i cant figure out a good way

@JzInqXc9Dg Not sure about good but I'd try this: CAST(REPLACE(REPLACE(...(REPLACE(REPLACE(YourColumn, '0', ''), '1', '') ...), '9', ''), '.', '') AS varbinary(100)).

3:46 PM

good tip @AndriyM

so it looks liek the only bad char i need to worry about is 0x0D - which is char(13)

so that means i am stripping out bad chars correctly.

i thought that was the reason my conversion is failing. in that case idk why my conversion is failing!

varchar -> decimal

3:59 PM

Perhaps posting a question on the main site could help.