问题描述:

I have a XML string that looks like

 <PLATFORM>

<PLATFORMNAME>UNIX</PLATFORMNAME>

<OS>Ipad</OS>

<VERSION>5.1</VERSION>

<PLATFORMNAME>Windows</PLATFORMNAME>

<OS>Windows</OS>

<VERSION>2008 (64bit)</VERSION>

</PLATFORM>

Which I need to insert into a temptable

 Create Table #TempXMLTable

(

PlatformName NVARCHAR(50),

OS NVARCHAR(50),

PlatformVersion NVARCHAR(50)

)

INSERT INTO #TempXMLTable VALUES(

@XML.value('(/PLATFORM/PLATFORMNAME)[1]', 'nvarchar(MAX)'),

@XML.value('(/PLATFORM/OS)[1]', 'nvarchar(MAX)') ,

@XML.value('(/PLATFORM/VERSION)[1]', 'nvarchar(MAX)'))

When I do the above insert only the first column is inserted but I want both inserts

Should look like -

 PlatformName OS Version

Unix Ipad 5.1

Windows Windows 2008

网友答案:
DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='
<OutLookContact>
<Contact FirstName="Asif" LastName="Ghafoor" EmailAddress1="[email protected]" />
<Contact FirstName="Rameez" LastName="Ali" EmailAddress1="[email protected]" />
<Contact FirstName="Aneel" LastName="Maqsood" EmailAddress1="[email protected]" />
</OutLookContact>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.

DECLARE @Temp TABLE(FirstName VARCHAR(250),LastName VARCHAR(250),Email1 VARCHAR(250))  

INSERT INTO @Temp(FirstName,LastName,Email1)

SELECT *

FROM OPENXML (@idoc, '/OutLookContact/Contact',1)

WITH (FirstName varchar(50),LastName varchar(50),EmailAddress1 varchar(50))


select FirstName,LastName,Email1 from @Temp
网友答案:

This query is good for up to 1023 platforms within the single XML variable. This constraint exists only because I used spt_values as the source of sequence numbers. You can use your own numbers or tally table if you need larger numbers.

INSERT INTO #TempXMLTable 
SELECT  --v.number,
        x.n.value('.','nvarchar(max)'),
        y.n.value('.','nvarchar(max)'),
        z.n.value('.','nvarchar(max)')
from @xml.nodes('/PLATFORM') a(b)
cross apply (select count(*) from @xml.nodes('//PLATFORMNAME') p(q)) c(countall)
join master.dbo.spt_values v on v.type='p' and v.number between 1 and countall
cross apply (select a.b.query('PLATFORMNAME[sql:column("number")]')) x(n)
cross apply (select a.b.query('OS[sql:column("number")]')) y(n)
cross apply (select a.b.query('VERSION[sql:column("number")]')) z(n)

However, what you actually have is an XML that is not actually best practice. You will notice that you have 2 sets of PLATFORMNAME,OS,VERSION that are identified only by ORDER within the same level. Best practice is to formulate the XML thus:

<PLATFORMS>
  <PLATFORM>
    <PLATFORMNAME>UNIX</PLATFORMNAME>
    <OS>Ipad</OS>
    <VERSION>5.1</VERSION>
  </PLATFORM>
  <PLATFORM>
    <PLATFORMNAME>Windows</PLATFORMNAME>
    <OS>Windows</OS>
    <VERSION>2008 (64bit)</VERSION>
  </PLATFORM>
</PLATFORMS>
相关阅读:
Top