XML Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML Help

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-09-06 : 13:55:51

I have an XML typed variable that contains a fragment like this:


declare @x xml
select @x = '<node att4="foo" att7="bar"/>'

I have no idea how many attributes will be on that node, but I need to output an XML fragment that looks like this:


<Thing name="att4" value="foo"/>
<Thing name="att7" value="bar"/>

I tried:


select
    1 as Tag,
    null as Parent,
    convert(nvarchar(max), a.query('for $c in ./@* return string(local-name($c))')) as 'Thing!1!name',
    convert(nvarchar(max), a.query('for $c in ./@* return string(data($c))')) as 'Thing!1!value'
from
    @x.nodes('/node') as result(a)
for XML explicit

But that gives me:


<Thing name="att4 att7" value="foo bar"/>

Help?

Jay
to here knows when

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 14:01:30

do you have to do this in sql?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-09-06 : 14:19:59

Eventually, no ... right now yes.

Let me explain (justify) what this is doing.

This is a generic data validation logging stored procedure. Some prior process has already gone though and marked rows as invalid. This proc comes along afterwords and looks for invalid rows. It dynamically generates my "<node>" fragment so that for each invalid row there is one "<node>" and the attributes are name/value pairs for each of the columns that make up the candidate key. That is why I don't know what the attributes will be called or how many of them there will be.

So once I have my fragement ("<error somecolum1="a" somecolum2="47"/><error somecolumn1="b" somecolumn2="47"/>") I need to take those candidate keys back to the original table to dynamically query (based on the error type) to get the bad value ... so need to dynamically build a where clause "where somecolumn1='a'" and somecolum2='47'".

That probably either doesn't make much sense or seem way more complicated that it needs to be. I'm planning on rewriting this entire process as SQLCLR, but for now, I need to get this working.

Jay

Jay
to here knows when

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 14:31:06

okie dokie.

will this do:


declare @x xml
select @x = '<node att4="foo" att7="bar"/>'

select
    a.query('for $c in ./@* 
		return  
                    <Thing name="{ string(local-name($c)) }" value="{ string(data($c)) }" />
	    ') as tempXml
from
    @x.nodes('/node') as result(a)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-09-06 : 14:31:07

Maybe this is a better question:
If I have:


declare @x xml
select @x = "<whereclause column1="a" column2="b"/><whereclause column1="c" column2="d"/>"

How do I convert it into a the where clause "where column1='a' and column2='b'"?

Presumably, I loop though @x.nodes('/whereclause[sql:variable("@LoopControlVariable")] to just get one at a time ...

Jay
to here knows when

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 14:44:11

well then this will do:


DECLARE @x XML
SELECT @x = '<whereclause column1="a" column2="b"/><whereclause column1="c" column2="d"/>'

DECLARE @finalXml XML
SELECT @finalXml = 
			(
				SELECT tempXml AS [text()]
				FROM 
				(
					SELECT
						CONVERT(NVARCHAR(MAX),
								a.query('for $c in ./@* 
									return  
											<Thing name="{ string(local-name($c)) }" 
												   value="{ string(data($c)) }" />
										')
								) AS tempXml
					FROM
						@x.nodes('/whereclause') AS result(a)

				) t
				FOR XML PATH('')
			)

-- remove the spaces in "& gt ;" and "& lt ;"
SELECT @finalXml = REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), @finalXml), '& gt ;', '>'), '& lt ;', '<') 
SELECT @finalXml

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 14:49:56

Yuck yuck yuck yuck yuck .....

Does XML really make this sort of things "better", or even "tolerable"?

Loads too much "We CAN xml, therefore we WILL xml" - there must be a Latin equivalent of "veni vidi vici"

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 14:53:17

well this is xml gone wrong

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 15:17:11

Thanks, you just made my point for me!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 15:25:12

yes. but xml done right is awsome!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-06 : 19:56:40

quote:
Originally posted by spirit1

yes. but xml done right is awsome!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Any example of where xml done right ?

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 02:39:17

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 04:41:44

in sql server these 2 come to mind first:
- service broker
- database mirroring

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 05:31:24

How does xml relate to database mirroring ? ?

KH
[spoiler]Time is always against us[/spoiler]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 05:50:57

i can't be sure about this of course (will have to test this

)
but since all operations over Endpoints in SQL Server are done using XML i assumed DB Mirroring won't differ here.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-09-07 : 08:39:02

hey hey hey ... ease up folks :)

Isn't everybody entitled to going down a bad path once? So, I made my first mistake ever ... I recognize it and it is on my short list of things to re-tool.

It is tough building a product sometimes ... especially one that is supposed to be -able (customizable, extensible, blah, blah, blah). Often times right gives way to generic.

Jay
to here knows when

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 08:40:33

quote:
Originally posted by spirit1

i can't be sure about this of course (will have to test this )

That's the job of Kristen

KH
[spoiler]Time is always against us[/spoiler]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 08:51:21

all i want to know jay is if the thing i posted helped.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-09-07 : 09:08:12

I guess it depends how you look at it ... yeah, your code helped me get past the latest bug and the code is once again working properly with this new scenerio.

But, no, your code mode it worse, because now my already bad proc filled with "that's not right" XML is has more of the same. :)

If only I hadn't been able to get it working, I would have been forced to re-write this crap ... not your fault ... mine.

Jay
to here knows when

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 09:09:50

well if you didn't want to make it working... WHY ON EARTH did you post it here?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources