Here is a quick example to shred that xml to a flat table:

DECLARE @xml XML

SET @xml = '<employees>

<employee>

<emp_id>1</emp_id>

<emp_name>Bob</emp_name>

<skills>

<skills_id>1</skills_id>

<skills_id>tsql</skills_id>

<skills_id>2</skills_id>

<skills_id>SSRS</skills_id>

<skills_id>3</skills_id>

<skills_id>SSAS</skills_id>

<skills_id>4</skills_id>

<skills_id>SSIS</skills_id>

<skills_id>5</skills_id>

<skills_id>Replication</skills_id>

</skills>

</employee>

<employee>

<emp_id>2</emp_id>

<emp_name>Frank</emp_name>

<skills>

<skills_id>1</skills_id>

<skills_id>tsql</skills_id>

<skills_id>2</skills_id>

<skills_id>SSRS</skills_id>

<skills_id>3</skills_id>

<skills_id>SSAS</skills_id>

</skills>

</employee>

</employees>

'

SELECT e.c.value('(emp_id)[1]', 'int') AS emp_id

, e.c.value('(emp_name/text())[1]', 'varchar(50)') AS emp_name

, s.c.value('(.)[1]', 'varchar(50)') AS skills_id

FROM...

Hi,

Personally, if I was to approach this from the SQL server side of things, I would do something like the following. It isn't pretty, but it works and I hope...

Hugo Kornelis (1/24/2013)

---

I think the correct answer should be worded slightly differently: "Either one can be appropriate" (italics used to emphasize changed part).

If forced to make a choice based on...

No problem.. Thanks for the feedback.

Hi,

I think you are very close, does this updated final query do what you need it to do or get you closer?

select

C.value('(Customers/CustomerID)[1]','INT') as CusID,

C.value('(Customers/ContactName)[1]','varchar(50)')...

Hi, One way of approaching it could be to use a CTE + sub query similar to this:

;WITH xCTE (CoveredEntity) AS

(

SELECT DISTINCT CoveredEntity

FROM @t

)

SELECT CoveredEntity AS '@Name',

(

SELECT CoverageLoser AS...

As Ray mentions, the xml methods are perfect for this.. here is an example based on your xml

DECLARE @xml XML

SET @xml = '<server name="Server1">

<log>

<logItem type="LogDate">

<value string="2012/12/15" />

</logItem>

<logItem type="error">

<value string="File not...

No problem....thanks for the feedback

Hi,

I think the main thing that you was missing was the namespace declaration. I have also made a couple of minor alterations, namely the cross apply and removing the .query()...

Hi,

I would use the newer XML functions rather then the old OPENXML. Syntax wise you could probably do this in a couple of ways, but here is one that I...

Lokesh Vij (11/30/2012)

---

Stewart "Arturius" Campbell (11/30/2012)

---

Koen Verbeeck (11/30/2012)

---

Got it wrong because you can update or delete rows in the primary tables if you specify ON CASCADE options.

Next time do more...

That is the crucial thing, as long...

If i understand you correctly, you have an xml instance as a string which you want to assign directly to an xml variable whilst at the same time, populating some...

Hi grovelli,

$x is a variable within a flwor statement. You can begin to learn about flwor statements...

Hi...