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.
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 XMLSET @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 XMLSET @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) |
|
|
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 |
|
|
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 tagsanyways: DECLARE @xmlPolicyType XMLSET @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 intEXEC sp_xml_preparedocument @hDoc output,@xmlPolicyType SELECT * FROM OPENXML(@hdoc, '/NewDataSet/PolicyType', 2) WITH (value varchar(5))EXEC sp_xml_removedocument @hDocI will try more later tonight. |
|
|
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>';-- SwePesoSELECT n.value('(.)', 'VARCHAR(20)') AS PolicyTypeFROM @xmlPolicyType.nodes('(NewDataSet/PolicyType)') AS r(n); Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|