Hi, xquery is just a language that you can use against an xml data type be it a variable or column. You can run it as part of a normal...

You can use a bit of xquery to transform the data. Here is an example to do that. Assuming the data is loaded into an xml variable called @xml

;with xmlnamespaces...

No problem... thanks for the feedback 🙂

I think the main issue that you may have had is the missing namespace declaration in your query. Your example XML has a namespace in there so you need to...

I wrote a blog on something similar a while back: http://www.olcot.co.uk/sql-blogs/using-xquery-to-create-a-single-xml-node-with-comma-separated-string-from-multiple-xml-nodes

here is one of a few ways to comma separate the node author for you:

DECLARE @xml XML

SELECT @xml = CONVERT(XML,...

Hi... Have you tried something like below.. This works fine with your example xml:

DECLARE @xml XML

SELECT @xml = CONVERT(XML, (SELECT * FROM OPENROWSET(BULK N'c:\testxml.xml', SINGLE_BLOB) AS xmldocument), 2)

SELECT t.c.value('(./text())[1]', 'varchar(30)')

FROM...

Hi, The reason why you are getting that message is because sp_xml_preparedocument does not support a typed xml document. Essentially a typed xml document is one that is bound by...

Thanks. glad it helped

my blog is syndicated right here at SSC http://www.sqlservercentral.com/blogs/rocks/

You can store it in a table or a variable and work with it ok. It just depends what you need to do with the XML.. Once you have shredded...

Nice one...

I think you are just missing the namespace reference as part of the XPath... Try this:

--now start shredding the xml

WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)

SELECT *,

...

Personally if I was working with an xml structure like that and had to populate a number of tables, then I would consider shredding it to a single temp table...

Hi Jim, It's probably me not understanding the data structure or maybe the example XML is missing some elements, but I can't see where all the columns map to the...

The example XML wasn't well formed so I have had to tweak it to make it valid.. To shred this you would need to use the nodes() function. Here is...

As Jeff points out, the xml isn't ideal which is true, but I have come up with something that makes a lot of assumptions!. Mainly around the ordering of the...