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
 General SQL Server Forums
 New to SQL Server Programming
 Help with XML

Author  Topic 

byka
Starting Member

18 Posts

Posted - 2014-07-31 : 12:53:22
why I can't see values from my xml?
DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
<NewDataSet>
<PolicyType>
MED
</PolicyType>
<PolicyType>
SPORT
</PolicyType>
</NewDataSet>
','utf-16','UTF-8')

DECLARE @xmlHandle INT
IF NOT @xmlPolicyType IS NULL
BEGIN

DECLARE @PolicyTypes TABLE
(
PolicyType CHAR(5)
)


/* Create the XmlDocument */
EXEC sp_xml_preparedocument @xmlHandle output, @xmlPolicyType

/* Use the OPENXML method to query the XmlDocument starting at /NewDataSet/PolicyType node. */
--INSERT INTO @PolicyTypes
SELECT PolicyType
FROM OPENXML (@xmlHandle, '/NewDataSet/PolicyType',1)
WITH ( PolicyType CHAR(5) '@PolicyType' )

/* Remove the document from memory */
EXEC sp_xml_removedocument @xmlHandle




SELECT t.value('(PolicyType/text())[2]','nvarchar(120)')AS PolicyType
FROM @xmlPolicyType.nodes('/NewDataSet/PolicyType')AS TempTable(t)


byka

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-31 : 13:30:06
Why are you using openxml? what are you trying to do?


DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
<NewDataSet>
<PolicyType>
MED
</PolicyType>
<PolicyType>
SPORT
</PolicyType>
</NewDataSet>
','utf-16','UTF-8')



SELECT
Elem.value('(PolicyType)[2]', 'VARCHAR(100)') as RecipientFirstName
FROM @xmlPolicyType.nodes('/NewDataSet') AS Parent(Elem)

Go to Top of Page

byka
Starting Member

18 Posts

Posted - 2014-07-31 : 13:42:36
I am trying to pass to SQL as many <PolicyType>
SPORT
</PolicyType> as I will have from .net and then insert into the temp table.
In your example you need to specify node# .I don't know how many I will have. How can I select all from XML?

byka
Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-31 : 14:31:51
Ahh..
Xml is always funny for me.

I was able to get this to work by adding another tag inside the xml. I am not sure if you have the ability to alter your xml you will be working with or if you would want to.

I also have a nodes example in my xml book, but that works when using XML attributes rather than tags

anyways:

DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
<NewDataSet>
<PolicyType><value>MED</value></PolicyType>
<PolicyType><value>SPORT</value></PolicyType>
</NewDataSet>
','utf-16','UTF-8')



DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc output,@xmlPolicyType

SELECT * FROM OPENXML(@hdoc, '/NewDataSet/PolicyType', 2) WITH
(value varchar(5))
EXEC sp_xml_removedocument @hDoc


I will try more later tonight.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-31 : 18:11:59
Why the replace at all?
DECLARE	@xmlPolicyType XML;

SET @xmlPolicyType = N'<?xml version="1.0" encoding="utf-16"?>
<NewDataSet>
<PolicyType>MED</PolicyType>
<PolicyType>SPORT</PolicyType>
</NewDataSet>';

-- SwePeso
SELECT n.value('(.)', 'VARCHAR(20)') AS PolicyType
FROM @xmlPolicyType.nodes('(NewDataSet/PolicyType)') AS r(n);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -